Geçtiğimiz ay SQL Server 2012 Always On mimarisine geçtik. Geçiş sırasında tüm databaseleri diğer replicalara restore etmemiz gerekiyordu. Transaction Log backuplarını yazmış olduğum script ile restore edebilirsiniz.
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