Merhaba;

Bir önceki yazımda bir klasördeki backup dosyalarının nasıl restore edileceğine bakmıştık. Bu yazımda da bir klasörde ki tüm transaction log backuplarının restore edilmesine  bakacağız.

Geçen haftalarda SQL Server 2012 Always On mimarisine geçtik. Geçiş sırasında hem full backup hem de Tlog backupların restore edilmesi gerekiyordu. Bu senaryoda bu script oldukça işime yaradı.

Scripti isterseniz SP haline dönüştürebilirsiniz. Parametrelerin açıklamalarını script içinde bulabilirsiniz.

SET NOCOUNT ON

DECLARE @DBList TABLE (rowid INT NOT NULL IDENTITY(1, 1), NAME VARCHAR(500))
DECLARE @backupFilesPath VARCHAR(256)
DECLARE @fileFormat VARCHAR(20)
DECLARE @cmd VARCHAR(max)
DECLARE @Ncmd NVARCHAR(4000)
DECLARE @fileName VARCHAR(256)
DECLARE @counter INT
DECLARE @startid INT
DECLARE @recoveryDB BIT
DECLARE @dataFiles TABLE (rowid INT, fileid INT, physicalname VARCHAR(256))
DECLARE @count INT
DECLARE @exec BIT
DECLARE @cmdshell TABLE (name nvarchar(200),minumum int,maximum int,config_value int, run_value int)

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

SET @backupFilesPath = 'D:\Backup' -- Where are bak files?
SET @fileFormat = 'trn'            -- File format? (no dot)
SET @recoveryDB = 1                -- 1: Restore with recovery, 0: Restore with NOrecovery
SET @exec = 0                      -- 1: Execute, 0: Only generate script

--======================================--

SET @startid = 1

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

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
 )

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

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

SET @cmd = 'DIR ' + @backupFilesPath + '\*.' + @fileFormat + ' /B'
SET @Ncmd = @cmd
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

INSERT INTO @cmdshell
EXEC sp_configure 'xp_cmdshell'

IF((SELECT config_value from @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 ('%.' + @fileFormat)

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;

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

SET @cmd = @cmd + '
RESTORE LOG [' + (
 SELECT databasename
 FROM ##headerTable
 ) + '] FROM DISK = N''' + @backupFilesPath + '\' + @filename + ''' WITH FILE = 1'

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

SET @cmd = @cmd + ', NOUNLOAD, STATS = 10
'
ContinueLoop:
 SET @startid = @startid + 1
END

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

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

DROP TABLE ##headerTable
SET NOCOUNT OFF