.- 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.
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
---------------------------------------------------------------------------------------------------
