Skapa ett dynamiskt restore skript

Många gånger kan det vara bra att ha färdiga skript för att göra restore av en databas. Antingen att man snabbt skall kunna göra restore vid en server eller databas krasch eller som i detta fall som jag tänkte visa, att man kontinuerligt restora databasen till en annan server. Tex för att kunna läsa datat där eller för att ha det som en säkerhets kopia. En fattigmans lösning för high availability helt enkelt.

Låt säga att vi har två servrar, Server1 och Server2. I detta fall så jobbar vi på Server2 där kopian skall ligga. Börja med att sätta upp en linked server, för det behöver vi för att kunna läsa backupinformationen i MSDB databasen på Server1.

Nedan följer kod för att lösa det hela. Först görs en full restore med NORECOVERY. Sedan restoras alla på följande transactionsloggar från efter att full backupen gick klart fram till @Stopat tiden som är vald. Utöka proceduren med mer logik om man önska loggning till en tabell eller retry funktion ifall tex backupfilen är låst av någon orsak. Så klart kan man bygga logik så att full restoren gör en RECOVERY ifall inga transactionsloggsbackuper finns.

Vill man kan man skriva det hela som ett restore skript med PRINT istället för EXEC och lägga ut det i en textfil. Dock inte beskrivet här.

ALTER PROCEDURE [dbo].[usp_RestoreScript] @DBname VARCHAR(100)

AS

SET NOCOUNT ON

DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastFullBackupFinishDate DATETIME, @i INT, @lastLogBackup INT, @logBackupPath VARCHAR(2000), @BackupFinishDate DATETIME, @Stopat DATETIME, @Message VARCHAR(MAX), @ErrorNumber INT, @ErrorLine INT

BEGIN

BEGIN

       -- Create temp table

       CREATE TABLE #MSDBBackupHistory (

       id INT IDENTITY(1,1),

       backup_set_id INT,

       media_set_id INT,

       position INT,

       backup_start_date DATETIME,

       backup_finish_date DATETIME,

       backup_type CHAR(1),

       physical_device_name VARCHAR(1000)

       )

       -- Dump the backup info into the temp table

INSERT INTO #MSDBBackupHistory (backup_set_id, media_set_id, position, backup_start_date, backup_finish_date, backup_type, physical_device_name)

SELECT bs.backup_set_id, bs.media_set_id, bs.position, bs.backup_start_date, bs.backup_finish_date, bs.type, RTRIM(bmf.physical_device_name) AS physical_device_name

              FROM LINKED_Server.msdb.dbo.backupset bs

                     JOIN LINKED_Server.msdb.dbo.backupmediafamily bmf

                            ON bmf.media_set_id = bs.media_set_id

       WHERE bs.database_name = @DBname

       ORDER BY bs.backup_start_date

BEGIN TRY

-- Get the last Full backup info.

SET @lastFullBackup = (SELECT MAX(id) FROM #MSDBBackupHistory WHERE backup_type='D')

SET @lastFullBackupPath = (SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastFullBackup)

SET @lastFullBackupFinishDate = (SELECT MAX(backup_finish_date) FROM #MSDBBackupHistory WHERE backup_type='D')

-- Restore the Full backup

DECLARE @SQL1 VARCHAR(MAX)

SET @SQL1 = 'RESTORE DATABASE ' + @DBName +' FROM DISK ='''+ @lastFullBackupPath + ''' WITH move ''LogicalName'' to ''E:\PR14_Data2\MSSQL11.DB_PR14\MSSQL\Data\DatabaseFileName.mdf'',

move ''LogicalNamelog'' to ''E:\PR14_Logs\MSSQL11.DB_PR14\MSSQL\Data\DatabaseFileName_log.ldf'',

NORECOVERY, REPLACE, STATS = 10'

EXEC (@SQL1)

--Set parameter for log restore

SET @i = (SELECT MIN(id) FROM #MSDBBackupHistory WHERE backup_type='L' and backup_start_date >= @lastFullBackupFinishDate )

SET @Stopat = (SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

SET @BackupFinishDate = (select MAX(backup_finish_date) from #MSDBBackupHistory where backup_finish_date <= @Stopat)

-- Restore the transactionlogs

WHILE (@i <= (SELECT MAX(id) FROM #MSDBBackupHistory where backup_finish_date <= @BackupFinishDate))

BEGIN

       

       SET @logBackupPath = (SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i)

       IF (@i = (SELECT MAX(id) FROM #MSDBBackupHistory where backup_finish_date <= @BackupFinishDate))

       SET @SQL1 = 'RESTORE LOG '+ @DBName +' FROM DISK = '''+ @logBackupPath + ''' WITH RECOVERY, STATS=5, STOPAT = '''+CAST(@Stopat as varchar(50))+''';'

       ELSE

       SET @SQL1 = 'RESTORE LOG '+ @DBName +' FROM DISK = '''+ @logBackupPath + ''' WITH NORECOVERY, STATS=5;'

       EXEC (@SQL1)

SET @i = @i + 1

END

--End restore logs  

END TRY

BEGIN CATCH

THROW;

END CATCH

END

-- remove temp objects that exist

IF OBJECT_ID('tempdb..#MSDBBackupHistory') IS NOT NULL

DROP TABLE #MSDBBackupHistory

END