(Latest Version 1.8 – 23 November 2020)
I’m sure we’ve all been there, we need to restore a database to 11:34am, four days ago. What’s the first step? To go off hunting down the relevant full and differential backups and then figure out what transaction logs we need to play in and in what order. Well here at SQL Undercover we’ve got the solution to all your restoration woes, the latest addition to the Undercover Toolbox, sp_RestoreScript.
sp_RestoreScript will do all the hard work for you, it’ll hunt down all the backup files that you need to restore your database to a specific time and present you with a nice, simple script to run based on the parameters that you give it.
Get the source code from our GitHub repository, https://github.com/SQLUndercover/UndercoverToolbox/blob/master/sp_restorescript.sql
NOTE: The code is no longer available directly from this page. Please visit our GitHub repo from the link above.
Prerequisites
Please note that if you’re running on a version of SQL Server prior to 2016 or have databases in a compatibility level earlier than 130, you’ll need our fn_SplitString
Parameter List (v1.7)
@DatabaseName
A comma delimited string containing all databases to restore, as of 1.4 @DatabaseName also accepts wildcards. DEFAULT: Current Database
@RestoreAsName
A comma delimited sting of names to restore databases as, the number of names should correspond to the number of @RestoreAsName. If wildcards have been specified in @DatabaseName, @RestoreAsName cannot be used.
@RestoreToDate
See @PointInTime for usage of @RestoreToDate. – DEFAULT: GETDATE()
@FirstLogToRestore
If the ‘LogsOnly’ restore option has been selected, this is the date of the first log to be restored. This can’t be left NULL if ‘LogsOnly’ has been selected. If any other option has been selected it will be ignored.
@RestoreOptions
‘ToLog’ – Script will generate statements including full, differential and log backups – DEFAULT
‘ToDiff’ Script will only include full and differential backups ‘ToFull’Script will only include a full backup
‘ToFull‘ Script will only include a full backup
‘LogsOnly’ – Script will only include log backups between @FirstLogToRestore and @RestoreToDate
@PointInTime
1 – Script will restore to a point in time specified in @RestoreToDate
0 – Script will to last backup before the date\time specified in @RestoreToDate – DEFAULT
@WithMoveDataPath
WITH MOVE Path to move data files to. – DEFAULT: original path
@WithMoveLogPath
WITH MOVE Path to move log files to. – DEFAULT:@ original path
@Replace
1 – Databases will be restored WITH REPLACE
0 – Database won’t be restored WITH REPLACE – DEFAULT
@NoRecovery
1- Last file will be restored with NORECOVERY, leaving the database in ‘restoring’ state
0 – Last file will be restored with RECOVERY and the database brought online – DEFAULT
@BrokerOptions – Valid options – ENABLE_BROKER, ERROR_BROKER_CONVERSATIONS, NEW_BROKER
@StandBy – Path for undo file, restores database in standby mode
@Credential – Credential to access Azure blob storage
@IncludeCopyOnly –
1 – Copy only backups are included
0 – Copy only backups are excluded
@SingleUser – Put the database into single user mode before restoring
@StopAtMark – Append stopatmark clause to any log restores
@StopBeforeMark – Append stopbeforemark clause to any log restores
Examples
Generate a restore script for the current database
EXEC sp_RestoreScript
Generate a restore script for a specified database
EXEC sp_RestoreScript @Databasename = 'SQLUndercover'
Generate a restore script for a multiple databases
EXEC sp_RestoreScript @Databasename = 'SQLUndercover,SomeOtherDB'
Generate a restore script using wildcards
EXEC sp_RestoreScript @Databasename = 'SQL%,SomeOtherDB'
Generate a restore script and rename a database
EXEC sp_RestoreScript @Databasename = 'SQLUndercover', @RestoreAsName = 'SQLUndercover_restored'
Restore a database to a point in time
EXEC sp_RestoreScript @Databasename = 'SQLUndercover', @RestoreToDate = '2017-06-29 13:28:59', @PointInTime = 1
Generate a List of Restore Statements For Transaction Log Backups Between Specified Dates
EXEC sp_RestoreScript @Databasename = 'SQLUndercover', @FirstLogToRestore = '2017-06-20 13:00:00', @RestoreToDate = '2017-06-29 13:00:00', @RestoreOptions = 'LogsOnly'
Restore Databases WITH MOVE
EXEC sp_RestoreScript @Databasename = 'SQLUndercover', @WithMoveDataPath = 'P:\SQLData', @WithMoveLogPath = 'Q:\SQLLogs'
Source Code
Get the source code from our GitHub repository, https://github.com/SQLUndercover/UndercoverToolbox/blob/master/sp_restorescript.sql
Bug Fixes
v1.3 – Command datatype changed to VARCHAR(MAX) – We were finding that restores consisting of a large number of backup files were exceeding the old VARCHAR(1000) datatype.
v1.8 – An invalid ALTER DATABASE statement was being generated when using @SingleUser with the WITH MOVE parameters. This is fixed in 1.8.
It is a pity that this script doesn’t auto generate statements for all user databases. Honestly most people who come here are probably looking for a solution to migrate multiple DBs from one VM to another. Some VMs have hundreds of DBs. This script is hard to use in that situation. Wish you can add that option. Otherwise, people, you can look at sp_RestoreGene. Thanks.
LikeLike
That’s not a bad suggestion, I’ll add it to the next version. 🙂
LikeLike
Hi David, thanks to you for this but it would be even better if we could give the path of the backup files as a parameter
LikeLiked by 1 person
Keep your eyes peeled, pointing at a file location is already on the list for the next version.
LikeLike