Instance İçerisindeki Tüm Data Dosyalarının(.mdf, .ldf, .ndf) Farklı Fiziksel Ortama Taşınması

Bir veritabanının data file'larının yerini değiştirmek için öncelikle taşınması istenen database'i offline moda getirmelisiniz.
.- alter database [Northwind] set offline-
 Daha sonra taşınması istenen database'in dosyalarını istenilen klasöre kopyala yapıştır yaparak taşıyabilirsiniz. Fakat SQL Server'a ilgili data file'ların yerini tanımlamanız gerekmektedir.
-alter database [Northwind] modify file (name =Northwind ,filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\northwnd.mdf')-
Bu tanımlamayı da yaptıktan sonra database'i
-alter database [Northwind] set online-
komutu ile online moda alabilirsiniz.

  Fakat eğer instance içerisinde taşınması gereken çok fazla Database'niz varsa ve hepsini taşımak istiyorsanız o zaman her biri için bu işlemi teker teker yapmak oldukça yorucu ve uzun zamanınızı alacaktır. Bunun için aslında işlemi tek sefer de yapabilecek bir script hazırladım.

Öncelikle Instance içerisindeki kullanıcı bazlı tüm database'leri offline moda almamız gerekecek. 

-----------------------------------------------------------------------------------------------

declare @sayac int

declare @dbname nvarchar(150)

declare @cumle nvarchar(350)

set @sayac = 10 -- sayac değişkenini 10 dan baslattim cunku 10 dan önceki Id'ler kullanıcı Database'leri değil ve bunları taşınmasını istemiyorum.

while (@sayac<14)

begin

set @dbname = (select db.[name] -- eğer offline moda alinmasini istemedğiniz database'niz varsa Where koşulunda ilgili database'in Id'sini belirterek işlem dışında tutabilirsiniz ki ben sayaç değişkenini 10 dan başlatarak bunları işlem dışında tuttum.

FROM sys.master_files as files

join master.dbo.sysdatabases as db on db.dbid=files.database_id

where physical_name like '%.mdf%'  and dbid = @sayac)

set @cumle = 'alter database ' +@dbname+ ' set offline'  -- online moda geçmek içinde buradaki offline'ı online olarak değiştirerek tüm kullanıcı database'lerini online moda alabilirsiniz.

exec (@cumle)

set @sayac =  @sayac +1

end 

------------------------------------------------------------------------------------------------------------

--select * from sys.databases
den bütün database'lerle ilgili bilgileri elde edebilirsiniz.

Daha sonra aşağıdaki kod ile instance içindeki bütün kullanıcı veritabanlarının sql server içerisindeki fiziksel dosya yolu tanımını değiştirebilirsiniz. Eğer işlem dışında tutmak istediğiniz veritabanı varsa kod içerisinde yorum satırı olarak belirttiğim yerlerde küçük değişikler yaparak bunu sağlayabilirsiniz. Script database içerisinde bulunan tüm .mdf, .ldf, ve vasrsa .ndf dosyalarını tespit edip taşınmasını sağlar. Siz sadece script içerisinde klasör yollarını bildirmeniz yeterli olacaktır. Bu işlemi yapmadan önce scripti test ortamında deneyerek test etmeyi unutmayın. Umarım faydalı olur. Bir sonraki yazıda görüşmek üzere hoşçakalın....

------------------------------------------------------------------------------------------------------

declare @maxx int 

declare @minn int

declare @db varchar(100)

declare @mdf varchar(100)

declare @ldf varchar(100)

declare @ndf varchar(100)

declare @ndfuzanti varchar(100)

declare @kodLDF varchar(300)

declare @kodMDF varchar(300)

declare @kodNDF varchar(300)

declare @ldfuzanti varchar(150)

declare @mdfuzanti varchar(150)

declare @ndfC int 

 set @minn = (select MIN(db.dbid)

--distinct db.dbid,db.name

FROM sys.master_files as files

join master.dbo.sysdatabases as db on db.dbid=files.database_id

where db.dbid>9) -- taşınması istenilen database Id'lerin boyutu 9'dan büyük olduğu için dbID>9 koşulu konulur. eğer taşınması istenmeyen veritabanları varsa Where koşulunda ilgili database ID lerini belirterek işlem dışında tutabilirsiniz.

--select *--MIN(db.dbid)

----distinct db.dbid,db.name

--FROM sys.master_files as files

--join master.dbo.sysdatabases as db on db.dbid=files.database_id

--where db.dbid>9

 set @maxx= (select max(db.dbid)

--distinct db.dbid,db.name

FROM sys.master_files as files

join master.dbo.sysdatabases as db on db.dbid=files.database_id

where db.dbid>9)

while (@minn<=@maxx)

begin

--ldf ve database name 

SELECT @db= db.[name], @ldf=files.[name],

@ldfuzanti=right(Physical_Name, charindex('\',reverse(physical_name))-1)

FROM sys.master_files as files

join master.dbo.sysdatabases as db on db.dbid=files.database_id

where physical_name like '%.ldf%' and db.dbid <>11 

--and database_id not in (1,2,3,4,5,6,7,8,9) 

and database_id = @minn

/*

eğer uzantısı .ldf ise bu log dosyasıdır.

Eğer uzantısı .mdf ise bu data dosyasıdır.

*/

--mdf için

SELECT @mdf=[name],

@mdfuzanti=right(Physical_Name, charindex('\',reverse(physical_name))-1)

FROM sys.master_files as files

where physical_name  like '%.mdf%' and database_id <>11 -- Taşınmasını istenmeyen database Id' yi burada belirtin. Mesela ben örnekte 11 Id'li database'in taşınmasını istemiyorum

--and database_id not in (1,2,3,4,5,6,7,8,9)

and database_id = @minn

if exists(

SELECT files.*

FROM sys.master_files as files

join master.dbo.sysdatabases as db on db.dbid=files.database_id

where physical_name like '%.ndf%' and db.dbid=@minn  and db.dbid <>11 )  -- Taşınmasını istenmeyen database Id' yi burada belirtin. Mesela ben örnekte 11 Id'li database'in taşınmasını istemiyorum.

begin 

declare @startt int

declare @ndfmax int

set @startt =1

set @ndfmax = ( SELECT count([name])

FROM sys.master_files as files

where physical_name like '%.ndf%' and database_id=@minn and database_id <>11 ) -- Taşınmasını istenmeyen database Id' yi burada belirtin. Mesela ben örnekte 11 Id'li database'in taşınmasını istemiyorum

SELECT [name],

right(Physical_Name, charindex('\',reverse(physical_name))-1) as ad,

row_number() over(order by right(Physical_Name, charindex('\',reverse(physical_name))-1)) as Id

into ##ndf

FROM sys.master_files as files

where physical_name like '%.ndf%' and database_id=@minn and database_id <>11 

while(@startt<=@ndfmax)

begin

-- for ndf:

SELECT 

@ndf=[name],

@ndfuzanti=ad

FROM ##ndf

where Id= @startt 

set @kodNDF = 'alter database' +'['+@db+']'+ 'modify file (name ='  +@ndf+ ' ,filename=''E:\Data\'+@ndfuzanti+''')' -- Burada taşınması istenilen klasörün yolunu bildirin.

exec(@kodNDF)

set @startt = @startt+1

end

drop table ##ndf

goto asda

end

else

asda:

begin

--ldf için 

set @kodLDF = 'alter database' +'['+@db+']'+ 'modify file (name ='  +@ldf+ ' ,filename=''E:\Data\'+@ldfuzanti+''')' -- Burada taşınması istenilen klasörün yolunu bildirin.

--mdf için  

set @kodMDF = 'alter database' +'['+@db+']'+ 'modify file (name ='  +@mdf+ ' ,filename=''E:\Data\'+@mdfuzanti+''')' -- Burada taşınması istenilen klasörün yolunu bildirin.

exec(@kodLDF)

exec(@kodMDF)

set @minn = @minn+1

end

end

---------------------------------------------------------------------------------------------------

SQL Server da Tüm Nvarchar(max) Alanların Tespit Edilip Düzenlenmesi

SQL Server da nvarchar(max) kullanımı database'i performans açısında negatif olarak etkiler. Tahmin edilebilir ve çok uzun değerler girilmeyecek alanlar için nvarchar(max)' tercih etmemek gerekir. Aşağıdaki kod'un çalışma mantığı database de tüm kullanıcı tablolarında kullanılan nvarchar(max) alanlarını tespit edip, o tablo içindeki maksimum değer girilen kolonun değerini bulur ve bu değerin %20'sini ekleyerek kolunun olması gereken karakter sınırını değiştirir. Eğer tablodaki kolonlarda 3000 den fazla girilmiş karakter varsa burada herhangi bir işlem yapmayacaktır. Bu durumda bu tablolar özelinde kendiniz gözden geçirip olması gereken sınıra karar vermelisiniz. Aksi taktirde bu işlem kritik bir müdahale olabilir. Eğer hiç veri girilmemiş bir kolon yada tablolarınız varsa burada scriptin referans alacağı bir kullanım değeri olmadığı için nvarchar(max) alanları default olarak 1500 ayarlanacaktır. Production ortamında kodu çalıştırmadan önce test ortamınız da test etmenizi öneriyorum. Umarım faydalı olur.

-------------------------------------------------------------------

DECLARE @tabload NVARCHAR(200)
DECLARE @maxdeger INT
DECLARE @kolonad NVARCHAR(200)
DECLARE @Id INT
DECLARE @FinalKomut NVARCHAR(400)
DECLARE @tabloCount INT
DECLARE @arakomut NVARCHAR(400)


SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 100)) AS ID,
TABLE_SCHEMA+'.'+TABLE_NAME as TabloAd,
COLUMN_NAME AS Kolon
INTO ##ym
FROM
INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE in('varchar','nvarchar') AND CHARACTER_MAXIMUM_LENGTH = -1 AND TABLE_SCHEMA not like 'dbo%'
AND TABLE_NAME <> 'Tablo ismi'  --Eğer işleme dahil edilmesini istemediğiniz kolon ve tablo ismi varsa burada belirtebilirsiniz.


SELECT @tabloCount=COUNT(*) FROM ##ym

SET @ID=0

yukari:
set @Id = @Id +1

WHILE (@Id<=@tabloCount)
begin

SELECT
@Id=Id ,
@tabload=tabload,
@kolonad=Kolon
FROM ##ym
WHERE ID=@Id


set @arakomut =(' set @maxdeger =(select max(len('+@kolonad+')) from '+@tabload+')')
exec sp_executesql @arakomut,N'@maxdeger int out',@maxdeger out

IF @maxdeger IS NULL or @maxdeger = ''  -- Eğer tablonun tüm satırları null yada boşsa default olarak nvarchar(1500) olarak ayarladım. Siz ihtiyaca göre buradaki alan değerini değiştirebilirsiniz.
begin
begin tran
SET @FinalKomut = 'alter Table '+@tabload+' alter column '+@kolonad+'  nvarchar(1500)'
EXEC sp_executesql @FinalKomut
print @tabload+' '+@kolonad+' Tablo Boş nvarchar(1500) olarak ayarlandı'
commit
goto yukari
end

IF (@maxdeger)>3000

BEGIN
begin tran
 PRINT @tabload+' '+@kolonad+' 3000 den büyük değer var.İşlem Yapılmayacak' -- Eğer karakter sayısı 3000'den fazla veri varsa burası kritik olduğu için herhangi bir işlem yapmamsını tavsiye ediyorum. Bu tablolar özelinde ekranları kullanan kişiler ile konuşuarak optimum bir değer belirlenmelidir.

commit
goto yukari

END

IF (@maxdeger)<3000 -- Eğer 3000 den küçükse tablo içerisnde maksimum karatker sayını bulup bunun %20'si kadar ekleyerek yeni nvarchar değerini atayacaktır.
BEGIN
begin tran
set @maxdeger = cast(@maxdeger*0.20 as int)+@maxdeger
SET @FinalKomut = 'alter Table '+@tabload+' alter column '+@kolonad+'  nvarchar('+cast(@maxdeger as varchar(20))+')'
EXEC sp_executesql @FinalKomut
commit
print @tabload+' '+@kolonad+' kolonu data siniri degistirildi.'
SET @ID= @ID+1
end
END