Script to construct a restore/recovery script
Run on a live production system to construct a recovery script from most recent full backup then all subsequent log file backups. Can override backup folder and data/log files WITH MOVE if restoring to a test server with different folder structure. Uses checksum where possible to verify backups. Ignores Symatech or other non SQL backups (VDI Devices) taken between full/transaction log backups. Lists backup files required for the recovery. The entire script, with the exception of the variable declarations and assignments, is actually a single SQL statement.
-- OVERRIDE VALUES FROM EXISTING
DECLARE @ToFileFolder VARCHAR(2000);
DECLARE @ToLogFolder VARCHAR(2000);
DECLARE @BackupDeviceFolder VARCHAR(2000);
SET @ToFileFolder = 'E:\Data\';
SET @ToLogFolder = 'D:\Logs\';
SET @BackupDeviceFolder = 'F:\Backups\';
--------------------------------------------------------------------------------------------------------------
-- Most recent full backup + subsequent log file backups
--------------------------------------------------------------------------------------------------------------
WITH CTE
(
database_name
,current_compatibility_level
,current_is_read_only
,current_state_desc
,current_recovery_model_desc
,has_backup_checksums
,[type]
,backupmediasetid
,backupfinishdate
,physical_device_name
)
AS
(
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,'D' AS [type]
,bs.media_set_id AS backupmediasetid
,x.backup_finish_date AS backupfinishdate
,mf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND b.[Device_Type] = 2
GROUP BY database_name
) x
ON x.database_name = bs.database_name
AND x.backup_finish_date = bs.backup_finish_date
JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
--------------------------
UNION
--------------------------
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,'L' AS [type]
,y.media_set_id AS backupmediasetid
,y.backup_finish_date as backupfinishdate
,mf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND b.[Device_Type] = 2
GROUP BY database_name
) x
ON x.database_name = bs.database_name
AND x.backup_finish_date = bs.backup_finish_date
INNER JOIN
(
SELECT
database_name
,backup_finish_date
,a.media_set_id
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'L'
AND b.[Device_Type] = 2
) y
ON y.database_name = bs.database_name
AND y.backup_finish_date > x.backup_finish_date
JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = y.media_set_id
)
---------------------------------------------------------------
-- RESULT SET COMES FROM HERE
SELECT
@@SERVERNAME
,a.Sequence
,a.Database_Name
,a.BackupDevice
,a.backupfinishdate
,a.Command
FROM
(
--------------------------------------------------------------------
-- Most recent full backup
--------------------------------------------------------------------
SELECT
';RESTORE DATABASE ' + d.[name] + SPACE(1) +
'FROM DISK = ' + '''' +
CASE ISNULL(@BackupDeviceFolder,'Actual')
WHEN 'Actual' THEN CTE.physical_device_name
ELSE @BackupDeviceFolder + SUBSTRING(CTE.physical_device_name,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 2, CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 1)
END + '''' + SPACE(1) +
'WITH REPLACE,' +
CASE CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM,' ELSE ' ' END +
'NORECOVERY,' + SPACE(1) +
'MOVE ' + '''' + x.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToFileFolder,'Actual')
WHEN 'Actual' THEN x.PhysicalName
ELSE @ToFileFolder + SUBSTRING(x.PhysicalName,LEN(x.PhysicalName) - CHARINDEX('\',REVERSE(x.PhysicalName),1) + 2, CHARINDEX('\',REVERSE(x.PhysicalName),1) + 1)
END + '''' + ',' + SPACE(1) +
'MOVE ' + '''' + y.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToLogFolder,'Actual')
WHEN 'Actual' THEN y.PhysicalName
ELSE @ToLogFolder + SUBSTRING(y.PhysicalName,LEN(y.PhysicalName) - CHARINDEX('\',REVERSE(y.PhysicalName),1) + 2, CHARINDEX('\',REVERSE(y.PhysicalName),1) + 1)
END + '''' AS Command,
1 AS Sequence,
d.name AS database_name,
CTE.physical_device_name AS BackupDevice,
CTE.backupfinishdate
FROM sys.databases d
JOIN
(
SELECT
DB_NAME(mf.database_id) AS name
,mf.Physical_Name AS PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = 'ROWS'
AND mf.file_id = 1
) x
ON d.name = x.name
JOIN
(
SELECT
DB_NAME(mf.database_id) AS name
,mf.Physical_Name PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = 'LOG'
) y
ON d.name = y.name
JOIN CTE
ON CTE.database_name = d.name
WHERE CTE.[type] = 'D'
AND d.name NOT IN ('master','model','msdb')
--------------------------------------------------------------------
UNION -- Restore Log backups taken since most recent full
--------------------------------------------------------------------
SELECT
';RESTORE LOG ' + d.[name] + SPACE(1) +
'FROM DISK = ' + '''' + --CTE.physical_device_name + '''' + SPACE(1) +
CASE ISNULL(@BackupDeviceFolder,'Actual')
WHEN 'Actual' THEN CTE.physical_device_name
ELSE @BackupDeviceFolder + SUBSTRING(CTE.physical_device_name,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 2, CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 1)
END + '''' +
' WITH NORECOVERY' AS Command,
32769 AS Sequence,
d.name AS database_name,
CTE.physical_device_name AS BackupDevice,
CTE.backupfinishdate
FROM sys.databases d
JOIN CTE
ON CTE.database_name = d.name
WHERE CTE.[type] = 'L'
AND d.name NOT IN ('master','model','msdb')
--------------------------------------------------------------------
UNION -- Restore WITH RECOVERY
--------------------------------------------------------------------
SELECT
';RESTORE DATABASE ' + d.[name] + SPACE(1) + 'WITH RECOVERY' AS Command,
32770 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
CTE.backupfinishdate
FROM sys.databases d
JOIN CTE
ON CTE.database_name = d.name
WHERE CTE.[type] = 'D'
AND d.name NOT IN ('master','model','msdb')
--------------------------------------------------------------------
UNION -- CHECKDB
--------------------------------------------------------------------
SELECT
';DBCC CHECKDB(' + '''' + d.[name] + '''' + ') WITH NO_INFOMSGS IF @@ERROR > 0 PRINT N''CONSISTENCY PROBLEMS IN DATABASE : ' + d.name + ''' ELSE PRINT N''CONSISTENCY GOOD IN DATABASE : ' + d.name + '''' AS Command,
32771 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
CTE.backupfinishdate
FROM sys.databases d
JOIN CTE
ON CTE.database_name = d.name
WHERE CTE.[type] = 'D'
AND d.name NOT IN ('master','model','msdb')
---------------------------------------------------------------------------------------------------------------------------------------------------
UNION -- MOVE Secondary data files
---------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
', MOVE ' + '''' + b.name + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToFileFolder,'Actual')
WHEN 'Actual' THEN b.physical_name
ELSE @ToFileFolder + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2, CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
END + '''',
b.file_id AS Sequence,
DB_NAME(b.database_id) AS database_name,
'' AS BackupDevice,
CTE.backupfinishdate
FROM sys.master_files b
INNER JOIN CTE
ON CTE.database_name = DB_NAME(b.database_id)
WHERE CTE.[type] = 'D'
AND b.type_desc = 'ROWS'
AND b.file_id > 2
) a
ORDER BY
database_name,
sequence,
backupfinishdate 
