Over the years I' found this to be a very hand stored proc to generate the required SQL Server
T-SQL RESTORE commands to recreate a recovered database out of the latest backup info:
dumps to disk (full/differerential/log) and the history tables from MSDB
I can not emphasize enough that the MSDB database has to be an integral part of you back strategy. I recommend configuring it in 'FULL' recovery mode and include it in your log backups
CREATE
PROCEDURE
[dbo].[usp_GenerateRestoreScripts] @DBname VARCHAR(100)
AS
SET
NOCOUNT
ON-- required because we're going to print T-SQL for the restores in the messages 'tab' of SSMS
/*
Script creates the T-SQL to restore a database with info from MSDB
It helps by creating RESTORE command constructed from the last FULL backup, the last DIFFERENTIAL backup
and all the required TRANSACTION LOG backups after this.
Neat when you have a high frequency of differential or log backups
The variable @DBName should be set to the name of the database you want to restore.
!!! BE AWARE: include MSDB in your backup plan for this T-SQL script to work in all circumstances !!!
I usually include MSDB in the log backup schedule (set the db to full recovery mode)
*/
DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)
DECLARE @i INT, @logBackupPath VARCHAR(1000)
-- remove temp object that might exist
IF
OBJECT_ID('tempdb..#MSDBBackupHistory')
IS
NOT
NULL
DROP
TABLE #MSDBBackupHistory
CREATE
TABLE #MSDBBackupHistory (
id INT
IDENTITY(1,1),
backup_start_date DATETIME,
backup_type CHAR(1),
physical_device_name VARCHAR(1000))
INSERT
INTO #MSDBBackupHistory (backup_start_date, backup_type, physical_device_name)
SELECT BS.backup_start_date, BS.type, RTRIM(BMF.physical_device_name)
FROM msdb..backupset BS JOIN msdb..backupmediafamily BMF ON BMF.media_set_id=BS.media_set_id
WHERE BS.database_name
= @DBName
ORDER
BY BS.backup_start_date
-- dump the last backup first in table
-- 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)
-- Restore the Full backup
PRINT
'RESTORE DATABASE '
+ @DBName
PRINT
'FROM DISK='''
+ @lastFullBackupPath +
''''
-- IF it's there's no backup (differential or log) after it, we set to 'with recovery'
IF
(@lastFullBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
PRINT
'WITH RECOVERY'
ELSE
PRINT
'WITH NORECOVERY'
PRINT
'GO'
PRINT
''
-- get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory WHERE backup_type='I'
AND id > @lastFullBackup)
SET @lastDifferentialBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastDifferentialBackup)
-- when there's a differential backup after the last full backup create the restore T-SQL commands
IF
(@lastDifferentialBackup IS
NOT
NULL)
BEGIN
-- Restore last diff. backup
PRINT
'RESTORE DATABASE '
+ @DBName
PRINT
'FROM DISK='''
+ @lastDifferentialBackupPath +
''''
-- If no backup made (differential or log) after it, set to 'with recovery'
IF
(@lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
PRINT
'WITH RECOVERY'
ELSE
PRINT
'WITH NORECOVERY'
PRINT
'GO'
PRINT
''
-- new line for readability
END
-- construct the required TRANSACTION LOGs restores
IF
(@lastDifferentialBackup IS
NULL)
-- no diff backup made?
SET @i = @lastFullBackup +
1 -- search for log dumps after the last full
ELSE
SET @i = @lastDifferentialBackup +
1
-- search for log dumps after the last diff
-- script T-SQL restore commands from the log backup history
WHILE
(@i <=
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
BEGIN
SET @logBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i)
PRINT
'RESTORE LOG '
+ @DBName
PRINT
'FROM DISK='''
+ @logBackupPath +
''''
-- it's the last transaction log, set to 'with recovery'
IF
(@i =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
PRINT
'WITH RECOVERY'
ELSE
PRINT
'WITH NORECOVERY'
PRINT
'GO'
PRINT
''
-- new line for readability
SET @i = @i +
1
-- try to find the next log entry
END
-- remove temp objects that exist
IF
OBJECT_ID('tempdb..#MSDBBackupHistory')
IS
NOT
NULL
DROP
TABLE #MSDBBackupHistory
