Generate restore scripts from backup history

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

Code for all sorts off backup

Backup is one off the most important things that need to be arranged after a SQL Server database is created, restored or attached.

The advantages of the T-SQL scripts presented here over a SSIS task:
1. Newly created db's are automatically included, no additional steps are required;
2. Databases that are in recovery mode 'Full' or 'Bulk Recovery' are automatically selected when transaction log backups are sheduled;
3. The checksum option that was introduced with SQL 2005 is included;
4. After a backup is completed a verify is executed;
5. Expired backups are automatically deleted;
6. Each set of dumps are stored in their own folder: less clutter
7. Databases can be set off-line without the job-step to fail
8. Databases in standby mode are skipped


The actual backup is made in the 'usp_MakeBackup' stored proc that is not called directly,
but is executed from the parent sp's: 'usp_MakeFullBackup', 'usp_MakeDifferentialBackup' and 'usp_MakeLogBackup' all scheduled from SQL Agent.


I usally make full backup around 2300hr and schedule log backups every 2hrs or so between 0800 until 2000hr.


The differential backup sp was only used in an OLTP config with a high volume of log backups (every 25 minutes), To avoid a very long list if individual log files we scheduled a differential backup every 4hrs. So the restore sequence was: full backup, last differential backup after the full backup and one or more transaction log backup that were made after the differential.

Code to initiate a full backup (store in CodeCatalog)

-- =========================================================================
-- Author:         SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a full backup
--                 The actual backup is made in sp 'usp_MakeBackup'
-- ========================================================================
CREATE PROCEDURE [dbo].[usp_MakeFullBackup]
AS
BEGIN
        SET NOCOUNT ON;

        DECLARE @DbName NVARCHAR(100)

        DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
           SELECT name FROM sys.databases
             WHERE name NOT IN ('tempdb','AdventureWorks') -- skip the unwanted DBs
               AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
               AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
            ORDER BY name

        OPEN dbnames_cursor
        FETCH NEXT FROM dbnames_cursor INTO @DbName

        WHILE @@FETCH_STATUS = 0
        BEGIN
             EXEC usp_MakeBackup @dbname, 'F' -- backup db here..

             FETCH NEXT FROM dbnames_cursor INTO @DbName
        END

        CLOSE dbnames_cursor
        DEALLOCATE dbnames_cursor


END
 

Code to initiate a differential backup (store in CodeCatalog)

-- ======================================================================
-- Author:    SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a differential backup
--              The actual backup is made in sp 'usp_MakeBackup'
-- ======================================================================
 
CREATE PROCEDURE [dbo].[usp_MakeDifferentialBackup]
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @DbName NVARCHAR(100)
 
--get all the on-line databases
       DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
           SELECT name FROM sys.databases-- skip unwanted DBs
            WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
              AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
              AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
           ORDER BY name
 
       OPEN dbnames_cursor
       FETCH NEXT FROM dbnames_cursor INTO @DbName
 
       WHILE @@FETCH_STATUS = 0
       BEGIN
              EXEC usp_MakeBackup @dbname, 'D' -- backup db here..
 
              FETCH NEXT FROM dbnames_cursor INTO @DbName
       END
 
       CLOSE dbnames_cursor
       DEALLOCATE dbnames_cursor
 
END
 

Code to initiate a transaction log backup (store in CodeCatalog)

-- ==============================================================
-- Author:    SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a log backup
--            The actual backup is made in sp 'usp_MakeBackup'
-- ==============================================================
CREATE PROCEDURE [dbo].[usp_MakeLogBackup]
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @DbName NVARCHAR(100)

 --get all the on-line databases
       DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
          SELECT name FROM sys.databases
              WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
                AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
                AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
                AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
           ORDER BY name

       OPEN dbnames_cursor
       FETCH NEXT FROM dbnames_cursor INTO @DbName

       WHILE @@FETCH_STATUS = 0
       BEGIN
              EXEC usp_MakeBackup @dbname, 'L' -- backup log here..

              FETCH NEXT FROM dbnames_cursor INTO @DbName
       END

       CLOSE dbnames_cursor
       DEALLOCATE dbnames_cursor
END
 

The actual backup to disk is made here (to store in CodeCatalog). Looks bit odd, still fighting the code editors:

-- ================================================================================
-- Author:        SQL Server advisor
-- Create date: jan/2009
-- Description:    makes full or logbackup based on passed parameter @BackupType
--            databases are selected in 'usp_MakeFullBackup',
--               'usp_MakeLogBackup' and usp_MakeDifferentialBackup
--=================================================================================

 --called from usp_MakeFullBackup, usp_MakeLogBackup or usp_MakeDifferentialBackup

CREATE PROCEDURE [dbo].[usp_MakeBackup] @DbName NVARCHAR(100), -- Database name
    @BackupType CHAR(1)
-- 'F' = full backup, 'L' = log backup, 'D' = differential backup
AS
BEGIN
    DECLARE @FileName NVARCHAR(300), @Directory NVARCHAR(256)
    DECLARE @BackupRetention SMALLINT   -- backup retention level, days
    DECLARE @DateToDelete DATETIME,@DeleteDateStr NVARCHAR(30),@FileExtension NCHAR(10)

    SET @Directory = 'D:\backup\' + @DbName -- set the backup path here

    SET @BackupRetention = 4 -- set retention &#8211;in days- of backup files here

    EXECUTE master.dbo.xp_create_subdir @Directory

    SET @FileName=@Directory + '\' + @DbName+'.'+Convert(nvarchar(10),GetDate(),104) + '_' +    LTrim(Str(DatePart(hour,GetDate())))+LTrim(Str(DatePart(minute,GetDate())))

    SET @DateToDelete=DateAdd(day,-ABS(@BackupRetention),GetDate())
    SET @DeleteDateStr=Convert(nvarchar(30),@DateToDelete,1) + ' 00:00'

    IF @BackupType = 'F' -- make a full backup
    BEGIN
        SET @FileExtension = 'BAK'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP DATABASE @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
    END

    IF @BackupType = 'D' -- make a differential  backup
    BEGIN
        SET @FileExtension = 'DIF'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP DATABASE @DbName TO DISK = @FileName WITH DIFFERENTIAL,
             
 CHECKSUM, INIT, STOP_ON_ERROR
    END

    IF @BackupType = 'L' -- make a log backup
    BEGIN
        SET @FileExtension = 'TRN'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP LOG @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
    END

--  check if backup was successfull
    RESTORE VERIFYONLY FROM DISK = @FileName WITH CHECKSUM, STOP_ON_ERROR

--  remove the backup files older then @BackupRetention days
    EXECUTE master.dbo.xp_delete_file 0,@Directory,@FileExtension,@DeleteDateStr,1;

END