It is handy to have a SQL Restore script ready to go for every backup that you run. Here is a script that will help with that.
-- available at http://databasehealth.com/shared-scripts/
-- Created by Steve Stedman
-- http://SteveStedman.com
-- http://StedmanSolutions.com
-- http://DatabaseHealth.com
-- twitter: @SqlEmt
-- email: Steve@StedmanSolutions.com
--
-- to use the procecure, first run the whole script to create it, then
-- uncomment the following line and replace database name with the name
-- of your database. Do not include square brackets on the database name.
-- NOTICE: the actual script may need to be modified for updated paths or
-- filenames.
-- examples
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory';
---- or
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'display';
---- or
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'FileInBackupDir';
-- to use the FileInBackupDir option you need to enable the 'Ole Automation Procedures'
-- uncomment and execute the following 8 lines.
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;
--GO
--sp_configure 'Ole Automation Procedures', 1;
--GO
--RECONFIGURE;
--GO
use DBHealthHistory; -- replace this with master, or your dba type database.
-- in case it exists and you need to drop it.
IF OBJECT_ID('dbhLogChain', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[dbhLogChain];
END
GO
CREATE PROCEDURE dbhLogChain @dbName VARCHAR(2048),
@outputSqlScript VARCHAR(20) = 'display'
-- database name should be quoted with no square brackets
AS
BEGIN
-- ============================================================================
-- Copyright (c) 2015-2017 Steve Stedman
-- http://DatabaseHealth.com
-- twitter: @SqlEmt
-- email: Steve@StedmanSolutions.com
-- ============================================================================
SET NOCOUNT ON;
IF LOWER(@dbName) = 'all_databases'
BEGIN
DECLARE @DatabaseID as INT;
DECLARE @DatabaseName as NVARCHAR(50);
DECLARE @DatabaseCursor as CURSOR;
SET @DatabaseCursor = CURSOR FOR
SELECT name, database_id
FROM sys.databases
WHERE name not in ('tempdb');
OPEN @DatabaseCursor;
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE [DBHealthHistory]..[dbhLogChain] @DatabaseName, 'FileInBackupDir';
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END
CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;
END
ELSE
BEGIN
DECLARE @outputBuffer AS VARCHAR(MAX) = '';
DECLARE @mostRecentBackupFile AS VARCHAR(MAX) = '';
DECLARE @maxDifferential AS DATETIME;
DECLARE @maxFull AS DATETIME;
--Use to enter extra comments
SET @outputBuffer += 'RAISERROR (''Caution running this script will overwrite the database [' + @dbName + '].'', 20, 1) WITH LOG; ' + char(13) + char(10);
SET @outputBuffer += '--Make sure to change restore file paths if needed' + char(13) + char(10);
SET @outputBuffer += '--Consider using STOPAT to recover mid transaction log. Syntax STOPAT = ''YYYYMMDD HH:MM:SS'' Add After NORECOVERY before STATS Example NORECOVERY, STOPAT = ''20150616 15:04:31'', STATS' + char(13) + char(10);
SELECT TOP 1 @maxFull = bs.backup_finish_date
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id
WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
AND NULLIF(bmf.physical_device_name, '') is not null
AND database_name = @dbName
AND bs.is_copy_only = 0
AND bs.is_snapshot = 0
AND bs.type = 'D' -- Full Database
ORDER by backup_set_id desc; -- DatabaseHealth
SELECT TOP 1 @maxDifferential = bs.backup_finish_date
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id
WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
AND NULLIF(bmf.physical_device_name, '') is not null
AND database_name = @dbName
AND bs.is_copy_only = 0
AND bs.is_snapshot = 0
AND bs.type = 'I' -- Differential
ORDER by backup_set_id desc; -- DatabaseHealth
IF @maxFull > @maxDifferential OR @maxDifferential is NULL
BEGIN
SET @maxDifferential = @maxFull;
END
SET @outputBuffer += 'use [master];' + char(13) + char(10);
SET @outputBuffer += '--ALTER DATABASE ' + QUOTENAME(@dbName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + char(13) + char(10);
SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) +
' FROM DISK = ''' + bmf.physical_device_name + ''' WITH <<<<<MOVES>>>>>--REPLACE, NORECOVERY, STATS = 1;' + char(13) + char(10),
@mostRecentBackupFile = bmf.physical_device_name
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id
WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
AND NULLIF(bmf.physical_device_name, '') is not null
AND database_name = @dbName
AND bs.is_copy_only = 0
AND bs.is_snapshot = 0
AND bs.type = 'D' -- full database
ORDER by backup_set_id desc;
IF NULLIF(@mostRecentBackupFile, '') IS NOT NULL
BEGIN
DECLARE @moves AS VARCHAR(MAX);
SET @moves = char(13) + char(10);
DECLARE @Table TABLE
(
[LogicalName] varchar(128),
[PhysicalName] varchar(128),
[Type] varchar,
[FileGroupName] varchar(128),
[Size] varchar(128),
[MaxSize] varchar(128),
[FileId] varchar(128),
[CreateLSN] varchar(128),
[DropLSN] varchar(128),
[UniqueId] varchar(128),
[ReadOnlyLSN] varchar(128),
[ReadWriteLSN] varchar(128),
[BackupSizeInBytes] varchar(128),
[SourceBlockSize] varchar(128),
[FileGroupId] varchar(128),
[LogGroupGUID] varchar(128),
[DifferentialBaseLSN] varchar(128),
[DifferentialBaseGUID] varchar(128),
[IsReadOnly] varchar(128),
[IsPresent] varchar(128),
[TDEThumbprint] varchar(128)
--,[SnapshotUrl] nvarchar(360) --- ONLY SQL 2016 or NEWER
);
INSERT INTO @table
EXEC('RESTORE FILELISTONLY FROM DISK=''' + @mostRecentBackupFile + ''' ');
SELECT @moves = @moves + '--MOVE ''' + LogicalName + ''' TO ''' + PhysicalName + ''',' + char(13) + char(10) from @Table;
SET @outputBuffer = REPLACE(@outputBuffer, '<<<<<MOVES>>>>>', @moves);
SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) +
' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 1; -- Differential' + char(13) + char(10),
@mostRecentBackupFile = bmf.physical_device_name
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id
WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
AND NULLIF(bmf.physical_device_name, '') is not null
AND database_name = @dbName
AND bs.backup_start_date > @maxFull
AND bs.is_copy_only = 0
AND bs.is_snapshot = 0
AND bs.type = 'I' -- differential
ORDER by backup_set_id DESC;
SELECT TOP 1 @mostRecentBackupFile = bmf.physical_device_name
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id
WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
AND NULLIF(bmf.physical_device_name, '') is not null
AND database_name = @dbName
AND bs.backup_start_date > @maxDifferential
AND bs.is_copy_only = 0
AND bs.is_snapshot = 0
AND bs.type = 'L' -- log backup
ORDER by backup_set_id DESC;
SELECT @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) +
' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 10; -- Log Backup' + char(13) + char(10)
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id
WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
AND NULLIF(bmf.physical_device_name, '') is not null
AND database_name = @dbName
AND bs.backup_start_date > @maxDifferential
AND bs.is_copy_only = 0
AND bs.is_snapshot = 0
AND bs.type = 'L' -- log backup
ORDER by backup_set_id ASC;
SET @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(@dbName) + ' WITH RECOVERY;' + char(13) + char(10);
SET @outputSqlScript = LOWER(@outputSqlScript);
IF @outputSqlScript = 'display'
BEGIN
PRINT @outputBuffer;
END
IF @outputSqlScript = 'fileinbackupdir'
BEGIN
DECLARE @ole INTEGER;
DECLARE @fileID INTEGER;
SET @mostRecentBackupFile = @mostRecentBackupFile + '.restore_script.sql';
--print @mostRecentBackupFile;
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @ole OUT;
EXECUTE sp_OAMethod @ole, 'OpenTextFile', @fileID OUT, @mostRecentBackupFile, 2, 1;
EXECUTE sp_OAMethod @fileID, 'WriteLine', Null, @outputBuffer;
EXECUTE sp_OADestroy @fileID;
EXECUTE sp_OADestroy @ole;
END
END
END
END
GO
EXECUTE [DBHealthHistory]..[dbhLogChain] 'ALL_DATABASES', 'FileInBackupDir';
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!