Auto-Generating RESTORE DATABASE Commands

Writing out the t-sql for a RESTORE DATABASE operation is not that big of a deal if you only do it once. But it gets tedious when you have to do it for multiple databases, or on a recurring basis, or if there are numerous transaction logs to be restored. I don't like the tedium any more than you do. So I decided to let t-sql do the work for me. Scripts for these three stored procedures are at the end of this post:

• dr.GenerateRestoreCommands: generates RESTORE commands for one (or all) databases for either the latest FULL or DIFFERENTIAL backup.
• dr.GenerateRestoreCommands_Log: generates RESTORE commands for one (or all) databases for transaction log backups taken after the most recent FULL or DIFFERENTIAL backup.
• dr.GenerateRestoreCommands_All: combines the two stored procs above, generating all RESTORE commands for a single database.

There are a couple of caveats that I know of. None of this will be of any use if your backup device type isn't disk (or tape). If your backups MIRROR TO one or more secondary devices, you'll need to tweak the SP's to specify which device to use (search for "AND MIRROR =" in the t-sql).

Here's an example that generates a RESTORE DATABASE command from the most recent full backup of the [MyDB] database:

EXEC dr.GenerateRestoreCommands @BackupType = 'D', @DBName = 'MyDB';

The screen output should look similar to the RESTORE command below. It specifies MOVE TO for each physical file. It also specifies the original paths for the mdf/ndf/ldf files. Change the paths as needed--there's a "TODO" comment in the output as a reminder.

RESTORE DATABASE MyDB
FROM
    DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MyDB.2015-10-13.FULL.1.bak',
    DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MyDB.2015-10-13.FULL.2.bak'
WITH 
    --TODO: replace the source database file paths below with the database file paths for the target.
    MOVE 'MyDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDB.mdf',
    MOVE 'MyDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDB.ldf',
    REPLACE, NORECOVERY 

To generate a RESTORE DATABASE command from the most recent DIFFERENTIAL backup of the [MyDB] database:

EXEC dr.GenerateRestoreCommands @BackupType = 'I', @DBName = 'MyDB';

To generate a RESTORE DATABASE command(s) from the LOG backup(s) of the [MyDB] database that occurred after the most recent FULL or DIFFERENTIAL backup:

EXEC dr.GenerateRestoreCommands_Log @DBName = 'MyDB';

To generate all of the above at once:

EXEC dr.GenerateRestoreCommands_All @DBName = 'MyDB';

Stored Procedure Definitions

IF NOT EXISTS (
    SELECT *
    FROM sys.schemas s
    WHERE s.name = 'dr'
)
EXEC ('CREATE SCHEMA dr AUTHORIZATION dbo')
GO

IF EXISTS (
 SELECT *
 FROM INFORMATION_SCHEMA.ROUTINES r
 WHERE r.ROUTINE_SCHEMA = 'dr' AND r.ROUTINE_NAME = 'GenerateRestoreCommands'
)
 DROP PROCEDURE dr.GenerateRestoreCommands 
GO

CREATE PROCEDURE dr.GenerateRestoreCommands
 @BackupType CHAR(1),
 @DBName SYSNAME = NULL
/*
 Purpose: 
 Generates RESTORE DB commands from existing backup history in msdb.
 Copy and paste the output into an SSMS window.
 
 Inputs:
 @BackupType :  valid values are 'D' (Full Backup) or 'I' (Differential Backup)
 @DBName : name of the database (NULL for all db's).

 History:
 11/06/2014 DMason Created
*/
AS
SET NOCOUNT ON

IF @BackupType NOT IN ('D', 'I')
BEGIN
 RAISERROR('Invalid value for input parameter @BackupType.  Valid values are ''D'' (Full Backup) or ''I'' (Differential Backup)', 16, 1);
 RETURN
END

SELECT Database_Name,
 MAX(Backup_Finish_Date) Backup_Finish_Date
INTO #LastFullBackups
FROM msdb.dbo.BackupSet bs
JOIN master.sys.databases d
 ON d.name = bs.database_name
WHERE Type = @BackupType
AND database_name = COALESCE(@DBName, database_name)
GROUP BY Database_Name

--RESTORE DB:  one row per db.
SELECT Database_Name, 'RESTORE DATABASE ' + Database_Name AS Command, CAST(1 AS NUMERIC(3,1)) AS CmdOrder
INTO #Commands
FROM #LastFullBackups

--FROM:  one row per db.
INSERT INTO #Commands (Database_Name, Command, CmdOrder)
SELECT Database_Name, 'FROM' AS Command, 2 AS CmdOrder
FROM #LastFullBackups

--DISK =:  one row per backup file per db.
;WITH BackupFileCount AS
(
 SELECT bs.Database_Name, MAX(bmf.Family_Sequence_Number) FileCount
 FROM msdb.dbo.BackupSet bs
 JOIN #LastFullBackups lfb
  ON lfb.Database_Name = bs.Database_Name
  AND lfb.Backup_Finish_Date = bs.Backup_Finish_Date
 JOIN msdb.dbo.BackupMediaFamily bmf
  ON bmf.Media_Set_Id = bs.Media_Set_Id
 --AND Mirror = 1
 GROUP BY bs.Database_Name
)
INSERT INTO #Commands (Database_Name, Command, CmdOrder)
SELECT bs.Database_Name, CHAR(9) + 'DISK = ''' + bmf.Physical_Device_Name + '''' + CASE WHEN bmf.Family_Sequence_Number = bfc.FileCount THEN '' ELSE ',' END, 3
FROM msdb.dbo.BackupSet bs
JOIN #LastFullBackups lfb
 ON lfb.Database_Name = bs.Database_Name
 AND lfb.Backup_Finish_Date = bs.Backup_Finish_Date
JOIN msdb.dbo.BackupMediaFamily bmf
 ON bmf.Media_Set_Id = bs.Media_Set_Id
JOIN BackupFileCount bfc
 ON bfc.Database_Name = bs.Database_Name
--AND Mirror = 1

--WITH:  one row per db.
INSERT INTO #Commands (Database_Name, Command, CmdOrder)
SELECT Database_Name, 'WITH ' AS Command, 4 AS CmdOrder
FROM #LastFullBackups

IF @BackupType = 'D'
BEGIN
 --Comment:  one row per db.
 INSERT INTO #Commands (Database_Name, Command, CmdOrder)
 SELECT Database_Name, CHAR(9) + '--TODO: replace the source database file paths below with the database file paths for the target.' AS Command, 4.5 AS CmdOrder
 FROM #LastFullBackups

 --MOVE:  one row per logical filename per db.
 INSERT INTO #Commands (Database_Name, Command, CmdOrder)
 SELECT bs.Database_Name, CHAR(9) + 'MOVE ''' + Logical_Name + ''' TO ''' + bf.Physical_Name + ''',', 5
 FROM msdb.dbo.BackupSet bs
 JOIN #LastFullBackups lfb
  ON lfb.Database_Name = bs.Database_Name
  AND lfb.Backup_Finish_Date = bs.Backup_Finish_Date
 JOIN msdb.dbo.backupfile bf
  ON bf.Backup_Set_Id = bs.Backup_Set_Id
END

--REPLACE, NORECOVERY:  one row per db.
INSERT INTO #Commands (Database_Name, Command, CmdOrder)
SELECT Database_Name, CHAR(9) + 'REPLACE, NORECOVERY ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) AS Command, 6 AS CmdOrder
FROM #LastFullBackups

DECLARE @Tsql VARCHAR(MAX)
SET @Tsql = ''

--Copy and paste this output into an SSMS window.
SELECT @Tsql = @Tsql + Command + CHAR(13) + CHAR(10)
FROM #Commands 
ORDER BY Database_Name, CmdOrder

PRINT @Tsql

--Cleanup
DROP TABLE #LastFullBackups
DROP TABLE #Commands
GO

IF EXISTS (
 SELECT *
 FROM INFORMATION_SCHEMA.ROUTINES r
 WHERE r.ROUTINE_SCHEMA = 'dr' AND r.ROUTINE_NAME = 'GenerateRestoreCommands_Log'
)
 DROP PROCEDURE dr.GenerateRestoreCommands_Log 
GO

CREATE PROCEDURE dr.GenerateRestoreCommands_Log
 @DBName SYSNAME = NULL
/*
 Purpose: 
 Generates RESTORE DB commands for transaction logs from existing backup history in msdb.
 Copy and paste the output into an SSMS window.
 
 Inputs:
 @DBName : name of the database (NULL for all db's).

 History:
 11/06/2014 DMason Created
*/
AS
SET NOCOUNT ON
DECLARE @Tsql VARCHAR(MAX) 
SET @Tsql = ''

;WITH LastFullOrDiffBackups AS
(
 SELECT bs.Database_Name,
  MAX(Backup_Finish_Date) LastBackup
 FROM msdb.dbo.BackupSet bs
 JOIN master.sys.databases d
  ON d.name = bs.database_name 
  AND d.name = COALESCE(@DBName, d.name)
 WHERE Type IN ('D', 'I')
 GROUP BY bs.Database_Name
)
--One row per transaction log backup per db.
--(assumes trx logs are backed up to a single file on disk).
SELECT @Tsql = @Tsql + 
 'RESTORE DATABASE ' + bs.Database_Name + CHAR(13) + CHAR(10) +
 CHAR(9) + 'FROM DISK = ''' + bmf.Physical_Device_Name + '''' + CHAR(13) + CHAR(10) +
 'WITH REPLACE, NORECOVERY ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM msdb.dbo.BackupSet bs
JOIN LastFullOrDiffBackups lfodb
 ON lfodb.Database_Name = bs.Database_Name
 AND bs.Backup_Finish_Date > lfodb.LastBackup
JOIN msdb.dbo.BackupMediaFamily bmf
 ON bmf.Media_Set_Id = bs.Media_Set_Id
--AND Mirror = 1
ORDER BY bs.Database_Name, bs.Backup_Finish_Date

PRINT @Tsql
GO

IF EXISTS (
 SELECT *
 FROM INFORMATION_SCHEMA.ROUTINES r
 WHERE r.ROUTINE_SCHEMA = 'dr' AND r.ROUTINE_NAME = 'GenerateRestoreCommands_All'
)
 DROP PROCEDURE dr.GenerateRestoreCommands_All 
GO

CREATE PROCEDURE dr.GenerateRestoreCommands_All
 @DBName SYSNAME
/*
 Purpose: 
 Generates RESTORE DB commands for a single database.
 The resulting output will restore the most recent FULL backup,
 followed by the most recent DIFFERENTIAL backup (if availabel),
 followed by the most recent LOG backups (if available).
 Copy and paste the output into an SSMS window.
 
 Inputs:
 @DBName : name of the database (NULL for all db's).

 History:
 11/06/2014 DMason Created
*/
AS
 SET NOCOUNT ON
 EXEC dr.GenerateRestoreCommands @BackupType = 'D', @DBName = @DBName
 EXEC dr.GenerateRestoreCommands @BackupType = 'I', @DBName = @DBName
 EXEC dr.GenerateRestoreCommands_Log @DBName = @DBName
GO