Merhaba;

Bu yazımda işinize yarayacağını düşündüğüm bir script paylaşmak istiyorum sizinle.

Çoğumuz belli bir serverda bulunan veritabanlarını başka bir servera taşıma ve kopyalama ihtiyacı duymuşuzdur. Ama sayı olarak çok fazla DB olduğunda bu işlem biraz yorucu ve sıkıcı olabilir 🙂

Bunun için bir SP yazdım.
Bu sp ile parametre olarak olarak belirttiğiniz klasördeki tüm .bak dosyalarının içini okuyup databaseleri servera restore edebilirsiniz. Ayrıca eğer varsa full text search dosyalarınıda restore edecektir.
Create scriptini aşağıda bulabilirsiniz. SPyi master db veya DBA scriptlerinizin olduğu veritabanında çalıştırabilirsiniz.

SPnin kullanımı şu şekilde;

EXEC sp_RestoreAllBackupFiles
		 @BackupPath = 'C:\Backup'
		,@DataPath = NULL
		,@LogPath = NULL
		,@Replace = 0
		,@Recovery = 1
		,@Execute = 0

Parametrelerin açıklaması;

CREATE PROC sp_RestoreAllBackupFiles

@BackupPath VARCHAR(256),
@DataPath VARCHAR(256) = NULL,
@LogPath VARCHAR(256) = NULL,
@Replace BIT = 0,
@Recovery BIT = 1,
@Execute BIT = 0

AS

SET NOCOUNT ON

DECLARE @DBList TABLE (rowid INT NOT NULL IDENTITY(1, 1), NAME VARCHAR(500))
DECLARE @backupFilesPath VARCHAR(256)
DECLARE @cmd VARCHAR(max)
DECLARE @Ncmd NVARCHAR(4000)
DECLARE @dataFilesPath VARCHAR(256)
DECLARE @logFilesPath VARCHAR(256)
DECLARE @dbname VARCHAR(500)
DECLARE @LogicalName VARCHAR(256)
DECLARE @PhysicalName VARCHAR(256)
DECLARE @LogicalName_log VARCHAR(256)
DECLARE @PhysicalName_log VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @counter INT
DECLARE @startid INT = 1
DECLARE @charIndex INT
DECLARE @MDF VARCHAR(256)
DECLARE @NDF VARCHAR(256)
DECLARE @LDF VARCHAR(256)
DECLARE @replaceDB BIT
DECLARE @recoveryDB BIT
DECLARE @dataFiles TABLE (rowid INT, fileid INT, physicalname VARCHAR(256))
DECLARE @count INT
DECLARE @ndfCmd VARCHAR(2000) = ''
DECLARE @exec BIT
DECLARE @cmdshell TABLE (name nvarchar(200),minumum int,maximum int,config_value int, run_value int)
DECLARE @isDataPathNull BIT
DECLARE @isLogPathNull BIT

------------------------------------
SET @backupFilesPath = @BackupPath
SET @dataFilesPath = @DataPath
SET @logFilesPath = @LogPath
SET @replaceDB = @Replace
SET @recoveryDB = @Recovery
SET @exec = @Execute
------------------------------------

SET @startid = 1
SET @ndfCmd = ''

IF (@dataFilesPath IS NOT NULL)
BEGIN
SET @isDataPathNull = 0
IF((SELECT RIGHT(@dataFilesPath,1)) = '\')
SET @dataFilesPath = LEFT(@dataFilesPath,LEN(@dataFilesPath)-1)
END
ELSE
SET @isDataPathNull = 1

IF (@logFilesPath IS NOT NULL)
BEGIN
SET @isLogPathNull = 0
IF((SELECT RIGHT(@logFilesPath,1)) = '\')
SET @logFilesPath = LEFT(@logFilesPath,LEN(@logFilesPath)-1)
END
ELSE
SET @isLogPathNull = 1

IF EXISTS (SELECT name FROM tempdb.sys.tables WHERE name = '##headerTable')
 DROP TABLE ##headerTable

IF EXISTS (SELECT name FROM tempdb.sys.tables WHERE name = '##fileListTable')
 DROP TABLE ##fileListTable

CREATE TABLE ##headerTable (
 [BackupName] NVARCHAR(128),
 [BackupDescription] NVARCHAR(255),
 [BackupType] SMALLINT,
 [ExpirationDate] DATETIME,
 [Compressed] TINYINT,
 [Position] SMALLINT,
 [DeviceType] TINYINT,
 [UserName] NVARCHAR(128),
 [ServerName] NVARCHAR(128),
 [DatabaseName] NVARCHAR(128),
 [DatabaseVersion] INT,
 [DatabaseCreationDate] DATETIME,
 [BackupSize] NUMERIC(20,0),
 [FirstLSN] NUMERIC(25,0),
 [LastLSN] NUMERIC(25,0),
 [CheckpointLSN] NUMERIC(25,0),
 [DatabaseBackupLSN] NUMERIC(25,0),
 [BackupStartDate] DATETIME,
 [BackupFinishDate] DATETIME,
 [SortOrder] SMALLINT,
 [CodePage] SMALLINT,
 [UnicodeLocaleId] INT,
 [UnicodeComparisonStyle] INT,
 [CompatibilityLevel] TINYINT,
 [SoftwareVendorId] INT,
 [SoftwareVersionMajor] INT,
 [SoftwareVersionMinor] INT,
 [SoftwareVersionBuild] INT,
 [MachineName] NVARCHAR(128),
 [Flags] INT,
 [BindingID] UNIQUEIDENTIFIER,
 [RecoveryForkID] UNIQUEIDENTIFIER,
 [Collation] NVARCHAR(128),
 [FamilyGUID] UNIQUEIDENTIFIER,
 [HasBulkLoggedData] BIT,
 [IsSnapshot] BIT,
 [IsReadOnly] BIT,
 [IsSingleUser] BIT,
 [HasBackupChecksums] BIT,
 [IsDamaged] BIT,
 [BeginsLogChain] BIT,
 [HasIncompleteMetaData] BIT,
 [IsForceOffline] BIT,
 [IsCopyOnly] BIT,
 [FirstRecoveryForkID] UNIQUEIDENTIFIER,
 [ForkPointLSN] NUMERIC(25,0),
 [RecoveryModel] NVARCHAR(60),
 [DifferentialBaseLSN] NUMERIC(25,0),
 [DifferentialBaseGUID] UNIQUEIDENTIFIER,
 [BackupTypeDescription] NVARCHAR(60),
 [BackupSetGUID] UNIQUEIDENTIFIER,
 [CompressedBackupSize] BIT --,
 --[containment] TINYINT
 )

IF ((SELECT SUBSTRING(CAST(SERVERPROPERTY('productversion') AS NVARCHAR),1, (CHARINDEX('.',CAST(SERVERPROPERTY('productversion') AS NVARCHAR))-1))) > 10)
BEGIN
 ALTER TABLE ##headerTable ADD [containment] TINYINT NULL
END
CREATE TABLE ##fileListTable (
 [LogicalName] NVARCHAR(128),
 [PhysicalName] NVARCHAR(260),
 [Type] CHAR(1),
 [FileGroupName] NVARCHAR(128),
 [Size] NUMERIC(20,0),
 [MaxSize] NUMERIC(20,0),
 [FileID] BIGINT,
 [CreateLSN] NUMERIC(25,0),
 [DropLSN] NUMERIC(25,0),
 [UniqueID] UNIQUEIDENTIFIER,
 [ReadOnlyLSN] NUMERIC(25,0),
 [ReadWriteLSN] NUMERIC(25,0),
 [BackupSizeInBytes] BIGINT,
 [SourceBlockSize] INT,
 [FileGroupID] INT,
 [LogGroupGUID] UNIQUEIDENTIFIER,
 [DifferentialBaseLSN] NUMERIC(25,0),
 [DifferentialBaseGUID] UNIQUEIDENTIFIER,
 [IsReadOnly] BIT,
 [IsPresent] BIT,
 [TDEThumbprint] VARBINARY(32)
 )

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

SET @cmd = 'DIR ' + @backupFilesPath + '\*.bak /B'
SET @Ncmd = @cmd

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

INSERT INTO @DBList
 EXEC xp_cmdshell @Ncmd

--Disable xp_cmdshell
 EXEC sp_configure 'show advanced options', 1
 RECONFIGURE
 EXEC sp_configure 'xp_cmdshell', 0
 RECONFIGURE
END
ELSE
BEGIN
 INSERT INTO @DBList (name)
 EXEC xp_cmdshell @Ncmd
END

DELETE
FROM @DBList
WHERE NAME IS NULL
 OR NAME NOT LIKE '%.bak'

IF((SELECT COUNT(*) from @DBList) = 0)
BEGIN
GOTO ExitCode
END

SET @cmd = ''

SELECT @counter = count(NAME)
FROM @DBList

WHILE (@startid <= @counter)
BEGIN
 SELECT @fileName = NAME
 FROM @DBList
 WHERE rowid = @startid

 DELETE
 FROM ##headerTable
 INSERT INTO ##headerTable
 EXEC ('RESTORE HEADERONLY FROM disk=''' + @backupFilesPath + '\' + @filename + '''')

 IF ((SELECT [DatabaseName] FROM ##headerTable) IN ('master', 'model', 'msdb', 'tempdb', 'distribution'))
 GOTO ContinueLoop;

 DELETE
 FROM ##fileListTable
 INSERT INTO ##fileListTable
 EXEC ('RESTORE FILELISTONLY FROM disk=''' + @backupFilesPath + '\' + @filename + '''')

SELECT @MDF = physicalname
 FROM ##fileListTable
 WHERE TYPE = 'D'
 AND fileid = 1

SET @MDF = REVERSE(@MDF)
 SET @charIndex = charindex('\', @MDF)
 SET @MDF = LEFT(@MDF, @charIndex - 1)
 SET @MDF = REVERSE(@MDF)

SELECT @LDF = physicalname
 FROM ##fileListTable
 WHERE type = 'L'

SET @LDF = REVERSE(@LDF)
 SET @charIndex = charindex('\', @LDF)
 SET @LDF = LEFT(@LDF, @charIndex - 1)
 SET @LDF = REVERSE(@LDF)

DELETE
 FROM @dataFiles

INSERT INTO @dataFiles
 SELECT ROW_NUMBER() OVER (
 ORDER BY fileid
 ) AS rowid
 ,fileid
 ,physicalname
 FROM ##fileListTable
 WHERE type IN ('D','F')
 AND fileid <> 1

SELECT @count = count(*)
 FROM @dataFiles
 WHERE fileid <> 1

WHILE (@count > 0)
 BEGIN
 SELECT @NDF = physicalname
 FROM @dataFiles
 WHERE rowid = @count

SET @NDF = REVERSE(@NDF)
 SET @charIndex = charindex('\', @NDF)
 SET @NDF = LEFT(@NDF, @charIndex - 1)
 SET @NDF = REVERSE(@NDF)

IF (@isDataPathNull = 1)
 BEGIN
 SET @dataFilesPath = REVERSE(SUBSTRING(REVERSE((SELECT physicalname FROM @dataFiles WHERE rowid = @count)),charindex('\', REVERSE((SELECT physicalname FROM @dataFiles WHERE rowid = @count)))+1,DATALENGTH(REVERSE((SELECT physicalname FROM @dataFiles WHERE rowid = @count)))))

END

SELECT @ndfCmd = @ndfCmd + ', MOVE N''' + (
 SELECT logicalname
 FROM ##fileListTable
 WHERE type IN ('D','F')
 AND fileid = (
 SELECT fileid
 FROM @dataFiles
 WHERE rowid = @count
 )
 ) + ''' TO N''' + @dataFilesPath + '\' + @NDF + ''''

 SET @count = (@count - 1)
 END

IF (@isDataPathNull = 1)
 BEGIN
 SET @dataFilesPath = REVERSE(SUBSTRING(REVERSE((SELECT physicalname FROM ##fileListTable WHERE type = 'D' AND fileid = 1)),charindex('\', REVERSE((SELECT physicalname FROM ##fileListTable WHERE type = 'D' AND fileid = 1)))+1,DATALENGTH(REVERSE((SELECT physicalname FROM ##fileListTable WHERE type = 'D' AND fileid = 1)))))

END

IF (@isLogPathNull = 1)
 BEGIN
 SET @logFilesPath = REVERSE(SUBSTRING(REVERSE((SELECT physicalname FROM ##fileListTable WHERE type = 'L')),charindex('\', REVERSE((SELECT physicalname FROM ##fileListTable WHERE type = 'L')))+1,DATALENGTH(REVERSE((SELECT physicalname FROM ##fileListTable WHERE type = 'L')))))

END

IF (@replaceDB = 1)
 BEGIN
 IF EXISTS (SELECT name FROM master.sys.databases WHERE name = (SELECT databasename FROM ##headerTable))
 BEGIN
 SET @cmd = @cmd + '
USE [master]
ALTER DATABASE ['+ (SELECT databasename FROM ##headerTable) + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
'
 END
 END

SET @cmd = @cmd + '
USE [master]
RESTORE DATABASE [' + (
 SELECT databasename
 FROM ##headerTable
 ) + '] FROM DISK = N''' + @backupFilesPath + '\' + @filename + ''' WITH FILE = 1, MOVE N''' + (
 SELECT logicalname
 FROM ##fileListTable
 WHERE type = 'D'
 AND fileid = 1
 ) + ''' TO N''' + @dataFilesPath + '\' + @MDF + ''', MOVE N''' + (
 SELECT logicalname
 FROM ##fileListTable
 WHERE type = 'L'
 ) + ''' TO N''' + @logFilesPath + '\'+ @LDF + '''' + @ndfCmd

IF (@replaceDB = 1)
 SET @cmd = @cmd + ', REPLACE'

IF (@recoveryDB = 0)
 SET @cmd = @cmd + ', NORECOVERY'

SET @cmd = @cmd + ', NOUNLOAD, STATS = 5
'

IF (@replaceDB = 1)
 BEGIN
 IF EXISTS (SELECT name FROM master.sys.databases WHERE name = (SELECT databasename FROM ##headerTable))
 BEGIN
 SET @cmd = @cmd + '
USE [master]
ALTER DATABASE ['+ (SELECT databasename FROM ##headerTable) + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE
'
 END
 END

ContinueLoop:
 SET @ndfCmd = ''
 SET @startid = @startid + 1
END

SET @cmd = '-- Start Script --
' + @cmd + '
-- End Script --'

IF ( @exec = 1 )
 BEGIN
 EXEC (@cmd)
 END
ELSE
 BEGIN
 SELECT @cmd
 END

ExitCode:
IF((SELECT COUNT(*) from @DBList) = 0)
BEGIN
 PRINT '- - - File(s) Not Found - - -'
END

DROP TABLE ##headerTable
DROP TABLE ##fileListTable
SET NOCOUNT OFF