Merhaba,

2 sql server arası bir veya daha fazla veri tabanını taşıma ihtiyacı her zaman olmuştur. Tek tek backup alıp, diğer tarafa kopyaladıktan sonra tek tek restore etmemiz gerekirdi. Eğer kopyalanacak veri tabanı sayısı fazla ise bu işlem çok uzun ve can sıkıcı olabiliyor.

Bu işlemleri otomatize etmek için bir script geliştirdim. Parametre olarak verdiğimiz veri tabanlarını ilgili serverlar arası backup-restore yöntemi ile kopyalıyor.
Script size yeni bir script üretecek. Bu scripti management studio’da “SQLCMD” modunda çalıştırmanız gerekiyor.

Always On ya da mirroring kurulumu sırasında bu script oldukça işe yarayacaktır.

Parametre detaylarını script içine yazdım. Anlaşılmayan noktalar için yorum yazabilirsiniz.

SET NOCOUNT ON

DECLARE @CmdBackup NVARCHAR(MAX)
DECLARE @CmdRestore NVARCHAR(MAX)
DECLARE @CmdDelete NVARCHAR(MAX)
DECLARE @CmdShell NVARCHAR(MAX)
DECLARE @CmdMain NVARCHAR(MAX)
DECLARE @DbNames NVARCHAR(MAX)
DECLARE @DbTbl TABLE (dbname NVARCHAR(500))
DECLARE @DestinationServer NVARCHAR(250)
DECLARE @TargetServerName NVARCHAR(250)
DECLARE @FilePathForXCopy NVARCHAR(250)
DECLARE @BackupFilePath NVARCHAR(250)
DECLARE @NoRecovery BIT
DECLARE @replaceDB BIT
DECLARE @BackupWithLog BIT
DECLARE @DataFilesPath NVARCHAR(250)
DECLARE @LogFilePath NVARCHAR(250)
DECLARE @DeleteFilesAfterRestore BIT
DECLARE @guid NVARCHAR(50)

-- PARAMETERS -- ================================================================================== 

SET @DestinationServer = @@SERVERNAME -- Kopyalanacak veri tabanlarinin durdugu server.
SET @TargetServerName = '<TargetServer>' -- Hangi server'a kopyalanacak.
SET @DbNames = 'DB1, DB2' -- Hangi veri tabanlari kopyalansin? (,) virgul ile birden fazla isim yazabilirsin.
SET @BackupFilePath = '<BackupFilePath>' -- Backup nereye alinsin?
SET @FilePathForXCopy = '' -- Backup dosyalari nereye kopyalansin? NULL ya da bos olursa kopyalama yapilmaz direkt restore baslatilir.
SET @DataFilesPath = 'C:\Data' -- Hedef serverda data dosyalari nerede dursun?
SET @LogFilePath = 'C:\Log' -- Hedef serverda log dosyalari nerede dursun?
SET @NoRecovery = 0 -- 0: WITH RECOVERY, 1: WITH NORECOVERY
SET @replaceDB = 0 -- 1: Eger hedef serverda veri tabani mevcutsa üzerine yazar.
SET @BackupWithLog = 0 -- 1: Transaction Log Backup da alir.
SET @DeleteFilesAfterRestore = 1 -- Restore isleminden sonra tüm bak dosyalari silinir.

-- PARAMETERS -- ==================================================================================

SET @guid = REPLACE(NEWID(),'-','')

IF (@DbNames = '' OR @DbNames IS NULL)
BEGIN
    SET @DbNames = 'select name from sys.databases where database_id > 4 and state = 0 ' + (CASE WHEN (@BackupWithLog = 1) THEN 'and recovery_model <> 3' ELSE '' END)
   
END
ELSE
BEGIN
    SET @DbNames = 'select ''' +  REPLACE(REPLACE(@DbNames,' ',''), ',', ''' union select ''') + ''''
END

INSERT INTO @DbTbl
EXEC sp_executesql @dbnames

DELETE FROM @DbTbl
WHERE dbname in (select name from sys.databases where  database_id <= 4 or state <> 0 OR recovery_model = (CASE WHEN (@BackupWithLog = 1) THEN 3 ELSE -1 END))

Backup_Part:

IF((SELECT RIGHT(@BackupFilePath,1)) = '\')
	SET @BackupFilePath = LEFT(@BackupFilePath,LEN(@BackupFilePath)-1)

SELECT @CmdBackup = COALESCE(@CmdBackup + '','') + 'BACKUP DATABASE [' + name + '] TO DISK = N''' + @BackupFilePath +  '\' + name + '_' + @guid + '.bak'' WITH NO_CHECKSUM, COMPRESSION
GO
' + (CASE WHEN (@BackupWithLog = 1) THEN 'BACKUP LOG [' + name + '] TO DISK = N''' + @BackupFilePath +  '\' + name + '_' + @guid + '.trn'' WITH NO_CHECKSUM, COMPRESSION
GO
' ELSE '' END)
FROM sys.databases
WHERE name in (SELECT dbname FROM @DbTbl)

SET @CmdBackup = '--========== BACKUP ==========--
:connect ' + @DestinationServer + '

' + @CmdBackup

--select @CmdBackup

XCopy_Part:

IF (@FilePathForXCopy IS NULL OR @FilePathForXCopy = '')
BEGIN
	SET @FilePathForXCopy = @BackupFilePath
	GOTO Restore_Part
END

IF((SELECT RIGHT(@FilePathForXCopy,1)) = '\')
	SET @FilePathForXCopy = LEFT(@FilePathForXCopy,LEN(@FilePathForXCopy)-1)

SET @CmdShell = 'EXEC xp_cmdshell ''XCOPY ' + @BackupFilePath + '\*' + @guid + '*.* ' + @FilePathForXCopy + ' /S /Y'', NO_OUTPUT
EXEC xp_cmdshell ''DEL ' + @BackupFilePath + '\*' + @guid + '*.*  /F /Q /S'', NO_OUTPUT'

IF((SELECT value FROM sys.configurations WHERE name = 'xp_cmdshell') = 0)
BEGIN
    SET @CmdShell = 'EXEC sp_configure ''show advanced options'', 1
RECONFIGURE
EXEC sp_configure ''xp_cmdshell'', 1
RECONFIGURE

' + @CmdShell + '

--Disable xp_cmdshell
EXEC sp_configure ''show advanced options'', 1
RECONFIGURE
EXEC sp_configure ''xp_cmdshell'', 0
RECONFIGURE'
END

SET @CmdShell = '
--========== XCOPY ==========--
' + @CmdShell + '
GO'

--select @CmdShell

Restore_Part:

SELECT @CmdRestore = COALESCE(@CmdRestore + '','') + 
(CASE WHEN (file_id = 1) THEN 
'RESTORE DATABASE [' + DB_NAME(database_id) + '] FROM DISK = N''' + @FilePathForXCopy +  '\' + DB_NAME(database_id) + '_' + @guid + '.bak'' WITH FILE = 1,
MOVE N''' + name + ''' TO N''' + @DataFilesPath + '\' + REVERSE(LEFT(REVERSE(physical_name), charindex('\', REVERSE(physical_name)) - 1)) + ''', 
'
WHEN (file_id not in (1,2)) THEN 
'MOVE N''' + name + ''' TO N''' + @DataFilesPath + '\' + REVERSE(LEFT(REVERSE(physical_name), charindex('\', REVERSE(physical_name)) - 1)) + ''', 
'
WHEN (file_id = 2) THEN 
'MOVE N''' + name + ''' TO N''' + @LogFilePath + '\' + REVERSE(LEFT(REVERSE(physical_name), charindex('\', REVERSE(physical_name)) - 1)) + ''', 
' + (CASE WHEN (@replaceDB = 1) THEN 'REPLACE, ' ELSE '' END) + 'NORECOVERY, NOUNLOAD, STATS = 5
GO
' +  (CASE WHEN (@BackupWithLog = 1) THEN 'RESTORE LOG [' + DB_NAME(database_id) + '] FROM DISK = N''' + @FilePathForXCopy +  '\' + DB_NAME(database_id) + '_' + @guid + '.trn'' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
' ELSE '' END)
ELSE '' END) + 
(CASE WHEN (@NoRecovery = 0 and file_id = 2) THEN 'RESTORE DATABASE [' + DB_NAME(database_id) + '] WITH RECOVERY
GO
' ELSE '' END)

FROM sys.master_files
WHERE DB_NAME(database_id) IN (SELECT dbname FROM @DbTbl)
ORDER BY DB_NAME(database_id) ASC, [type] ASC, file_id ASC

SET @CmdRestore = '

--========== RESTORE ==========--
:connect ' + @TargetServerName + '

' + @CmdRestore

--select @CmdRestore

Delete_Part:
IF @DeleteFilesAfterRestore = 0
	GOTO Script_Part

SELECT @CmdDelete = COALESCE(@CmdDelete + '','--========== DELETE ==========--') + '
EXEC xp_delete_file 0, N''' + @FilePathForXCopy +  '\' + name + '_' + @guid + '.bak'', ''Bak'',''Oct 01 2020  1:00PM'', 0
GO'

FROM sys.databases
WHERE name in (SELECT dbname FROM @DbTbl)


Script_Part:
SET @CmdMain = '/*** EXECUTE IN SQLCMD MODE ***/

' + @CmdBackup + ISNULL(@CmdShell,'') + @CmdRestore + ISNULL(@CmdDelete,'')

SELECT @CmdMain
GO