You use Ola Hallengren’s maintenance scripts to do your database backups on SQL Server 2016 or newer, and you want to do things like:
- Restore the most recent database backup
- Test it for corruption
- Initialize restores to get ready for log shipping or replication
That’s where the open source sp_DatabaseRestore comes in, from our free First Responder Kit. Here’s one way to run it:
|
1 2 3 4 5 6 |
EXEC dbo.sp_DatabaseRestore @Database = 'StackOverflow', @BackupPathFull = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\FULL\', @BackupPathLog = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\LOG\', @ContinueLogs = 0, @RunRecovery = 1; |
Input parameters include:
- @Database NVARCHAR(128) – name of the source database
- @RestoreDatabaseName NVARCHAR(128), default=NULL – name of the restored database, can leave off or set to NULL if the restored name will be the source database’s name
- @BackupPathFull NVARCHAR(MAX) – full path with ending backslash where the FULL backups are stored
- @BackupPathLog NVARCHAR(MAX) – full path with ending backslash where the LOG backups are stored
- @MoveFiles BIT, default=0 – whether or not you want to use a different location for the database files than what was used on the source server, leave off or set to 0 if using the same path as the source
- @MoveDataDrive NVARCHAR(260), default=NULL – new location for the data file(s), used when @MoveFiles=1
- @MoveLogDrive NVARCHAR(260), default=NULL – new location for the log file, used when @MoveFiles=1
- @TestRestore BIT, default=0 – whether or not you are testing restores, if set to 1 then it drops the database at the end
- @RunCheckDB BIT, default=0 – whether or not you want it to run DBCC CHECKDB after the restore, it assumes you are using Ola’s DatabaseIntegrityCheck stored procedure
- @ContinueLogs, default=0 – whether or not you are continuing to restore logs after the database has already been restored without recovering it
- @RunRecovery BIT, default=0 – whether or not to recover the database (RESTORE DATABASE WITH RECOVERY so that it is now usable)
- @StopAt, NVARCHAR(14) null – if you want to stop restoring logs as of a certain time, you can pass in a date/time pass in a date time like ‘20170508201501’. This doesn’t use the StopAt command for restores – it simply doesn’t restore transaction logs that have this date/time in it.
Some common ways to call it include:
Restore the newest FULL backup and all LOG backups but keep it in the NORECOVERY state so that more LOG backups can be applied:
|
1 2 3 4 5 6 |
EXEC dbo.sp_DatabaseRestore @Database = 'StackOverflow', @BackupPathFull = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\FULL\', @BackupPathLog = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\LOG\', @ContinueLogs = 0, @RunRecovery = 0; |
|
1 2 3 4 5 6 |
EXEC dbo.sp_DatabaseRestore @Database = 'StackOverflow', @BackupPathFull = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\FULL\', @BackupPathLog = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\LOG\', @ContinueLogs = 1, @RunRecovery = 0; |
|
1 2 3 4 5 6 |
EXEC dbo.sp_DatabaseRestore @Database = 'StackOverflow', @BackupPathFull = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\FULL\', @BackupPathLog = '\\BackupServer\SQLBackups\SQL2022PROD1A\StackOverflow\LOG\', @ContinueLogs = 1, @RunRecovery = 1; |
For Support
- Check out the documentation for the rest of the parameters
- When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs. When you find a bug or want something changed, read the contributing.md file.
