Restore SQL Server Databases from Ola Hallengren Backups
The hardest part about restoring a database is generating the restore script. That’s why I wrote dbo.RestoreDatabase. It builds the restore for you, and it can run it too if you let it.
This is for Ola Hallengren’s SQL Server Maintenance Solution only. I’m serious. It wants Ola’s DatabaseBackup folders: a root directory, a folder named after the database, then FULL, DIFF, and LOG files sitting where Ola puts them. A folder full of random .bak files is not a restore plan, and this procedure will not sort them for you. It is not a translator for some other vendor’s folder layout either. If you are not backing up with Ola, stop. This procedure is not for you.
Run it from master. @execute = 'N' prints the commands. 'Y' actually runs them. Start with N. Your future self will thank you.
View the procedure here: RestoreDatabase.sql.
Example 1: restore TaylorSwiftDB to a test box
SIMPLE recovery, shrink the log, move the files onto different drive letters. This one executes, so don’t paste it into prod for fun.
use master
exec dbo.RestoreDatabase
@database = 'TaylorSwiftDB'
,@root_backup_directory = '\\testserver\G$\testserver'
,@optional_restore_name = NULL
,@execute = 'Y'
,@new_server_data_drive_and_directory = 'X:\'
,@new_server_log_drive_and_directory = 'Y:\'
,@intended_target_server = 'test\test'
,@restore_datetime = NULL
,@recovery_method = 'SIMPLE'
,@is_full_copy_only = 'N'
,@is_shrink_log_after_restore = 'Y'
,@check_restore_time_integrity = 'N'
,@restore_offset_allowance_minutes = 30
,@use_logs_for_restore_path = 'Y'
,@use_differentials_for_restore_path = 'Y'
,@is_clear_backup_history = 'N'
,@backup_software = 'NATIVE'
,@change_db_owner_to_sa = 'N'
,@use_replace_for_restore = 'N'
,@stats = 0
,@check_db = 'N'
,@enable_broker = NULL
,@drop_after_restore = 'N'
,@standby_file_directory = NULL
,@is_create_snapshot = 'N'
,@use_estimates_for_headeronly_metadata = 'N'
Example 2: same restore, print only
Same call as above, except @execute = 'N'. It prints the restore script and leaves the database alone.
use master
exec dbo.RestoreDatabase
@database = 'TaylorSwiftDB'
,@root_backup_directory = '\\testserver\G$\testserver'
,@optional_restore_name = NULL
,@execute = 'N'
,@new_server_data_drive_and_directory = 'X:\'
,@new_server_log_drive_and_directory = 'Y:\'
,@intended_target_server = 'test\test'
,@restore_datetime = NULL
,@recovery_method = 'SIMPLE'
,@is_full_copy_only = 'N'
,@is_shrink_log_after_restore = 'Y'
,@check_restore_time_integrity = 'N'
,@restore_offset_allowance_minutes = 30
,@use_logs_for_restore_path = 'Y'
,@use_differentials_for_restore_path = 'Y'
,@is_clear_backup_history = 'N'
,@backup_software = 'NATIVE'
,@change_db_owner_to_sa = 'N'
,@use_replace_for_restore = 'N'
,@stats = 0
,@check_db = 'N'
,@enable_broker = NULL
,@drop_after_restore = 'N'
,@standby_file_directory = NULL
,@is_create_snapshot = 'N'
,@use_estimates_for_headeronly_metadata = 'N'Example 3: point in time, different name
TaylorSwiftDB on reporting got dropped. This brings it back as TaylorSwiftDBReporting, exactly as of 2021-10-22 14:25:06.467, and flips it to SIMPLE.
use master
exec master.dbo.RestoreDatabase
@database = 'TaylorSwiftDB'
,@root_backup_directory = '\\testserver\G$\testserver'
,@optional_restore_name = 'TaylorSwiftDBReporting'
,@execute = 'Y'
,@new_server_data_drive_and_directory = 'X:\'
,@new_server_log_drive_and_directory = 'Y:\'
,@intended_target_server = 'test\test'
,@restore_datetime = '2021-10-22 14:25:06.467'
,@recovery_method = 'SIMPLE'
,@is_full_copy_only = 'N'
,@is_shrink_log_after_restore = 'N'
,@check_restore_time_integrity = 'Y'
,@use_logs_for_restore_path = 'Y'
,@use_differentials_for_restore_path = 'Y'
,@is_clear_backup_history = 'N'
,@backup_software = 'NATIVE'
,@use_replace_for_restore = 'Y'
,@stats = 0
,@check_db = 'N'
,@drop_after_restore = 'N'
,@standby_file_directory = NULL
,@is_create_snapshot = 'N'
,@use_estimates_for_headeronly_metadata = 'N'FAQ: Restoring SQL Server from Ola Hallengren backups
How do I restore a SQL Server backup from Ola Hallengren backups?
Ola’s DatabaseBackup drops FULL, DIFF, and LOG files into folders. Great. You still have to figure out which ones to restore and in what order. That’s the annoying part. dbo.RestoreDatabase reads those folders, builds the restore script, and can run it if you let it.
Can I restore a SQL Server database from an Ola backup (FULL / DIFF / LOG)?
Yes. Point @root_backup_directory at your Ola root (server / database / FULL|DIFF|LOG). It picks the right full, any differential, and the log chain so you are not writing restores by hand. Sometimes that is one file. Sometimes the log chain got long and you are looking at thousands of statements. Either way, you are not typing them.
What’s the difference between this and just using Ola’s scripts?
Ola backs up. dbo.RestoreDatabase restores. You point it at your Ola root, give it a database name, and it builds the whole chain (full, diff, logs) in one pass. If you need to step through logs one at a time, it can print the STANDBY restores so you can inspect between each one. It also checks that the restore actually lands within a set window of your newest backup, not just that a restore command ran. It can also shrink the log, run CHECKDB, drop the copy afterward for restore tests, and more. The full list is in Parameters below.
@Execute decides whether it just prints the script or actually runs it. No GUI clicking, no handwritten restore scripts to babysit. The output is clean too: section separators, aligned columns, no wall of raw commands to squint at. It’s been doing that for years, since before sp_DatabaseRestore and dbatools showed up. If Brent’s tool or dbatools already fit your workflow, keep using them, they’re solid. Mine just goes about it a little differently and has its own set of features.
Do I need anything besides Ola’s backup folders?
A path SQL Server can read, and permission to restore. That’s the shopping list. And it has to be Ola’s folder layout. If your backups live in some other scheme, this procedure is going to be a bad roommate.
Example call
The call that produced the demo output below.
exec dbo.RestoreDatabase
@database = 'TaylorSwiftDB'
,@root_backup_directory = '\\GsNetworkShare\G$\TSwift-SQL\'
,@optional_restore_name = 'FearlessDB'
,@execute = 'Y'
,@intended_target_server = 'TSwift-SQL'
,@use_differentials_for_restore_path = 'Y'
,@use_logs_for_restore_path = 'Y'
Example output
Demo run: one full, one differential, two log backups.
/*
Date and time: 2021-10-22 17:01:59.907
Server: TSwift-SQL
Version: 14.0.3505.1
Edition: Developer Edition (64-bit)
Parameters:
@database = 'TaylorSwiftDB'
@root_backup_directory = '\\GsNetworkShare\G$\TSwift-SQL\'
@optional_restore_name = 'FearlessDB'
-- *------------------------------------------------------------
-- * - @restore_datetime = NULL (latest backup available)
-- *------------------------------------------------------------
,@intended_target_server = 'TSwift-SQL'
,@execute = 'Y'
/************** Start: These values are computed from the @root_backup_directory *************************************/
,@root_backup_directory = '\\GsNetworkShare\G$\TSwift-SQL\'
,@FullSourceDirectory = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL\'
,@FullSourceDirectoryCopyOnly = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL_COPY_ONLY\'
,@DifferentialDirectorySourceLocation = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\DIFF\'
,@LogDirectorySourceLocation = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\LOG\'
/************* End: values are computed from the @root_backup_directory **********************************************/
,@new_server_data_drive_and_directory = 'AutoSelect - (looking up data drive/folder from list stored inside this SP)'
,@new_server_log_drive_and_directory = 'AutoSelect - (looking up log drive/folder from list stored inside this SP)'
,@recovery_method = 'NULL'
,@is_full_copy_only = 'N'
,@is_shrink_log_after_restore = 'N'
,@check_restore_time_integrity = 'Y'
,@restore_offset_allowance_minutes = 30
,@change_restore_offset_for_simple_recovery_or_no_log_restores = 1440
,@use_logs_for_restore_path = 'Y'
,@use_differentials_for_restore_path = 'Y'
,@is_clear_backup_history = 'N'
,@backup_software = 'NATIVE'
,@change_db_owner_to_sa = 'N'
,@use_replace_for_restore = 'Y'
,@stats = 0
,@check_db = 'N'
,@enable_broker = 'NULL'
,@drop_after_restore = 'N'
,@standby_file_directory = 'NULL'
,@is_create_snapshot = 'N'
,@use_estimates_for_headeronly_metadata = 'N'
*/
use master;
set nocount on;
set deadlock_priority high;
set transaction isolation level read uncommitted;
-- ***********************************************************************************************************************************
-- * -- Full Restore -- *
-- ***********************************************************************************************************************************
restore database [FearlessDB] from
disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL\TSwift-SQL_TaylorSwiftDB_FULL_20211022_015902_1.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL\TSwift-SQL_TaylorSwiftDB_FULL_20211022_015902_2.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL\TSwift-SQL_TaylorSwiftDB_FULL_20211022_015902_3.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL\TSwift-SQL_TaylorSwiftDB_FULL_20211022_015902_4.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL\TSwift-SQL_TaylorSwiftDB_FULL_20211022_015902_5.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\FULL\TSwift-SQL_TaylorSwiftDB_FULL_20211022_015902_6.bak'
with FILE = 1, REPLACE, NORECOVERY,
move 'TaylorSwiftDB_Log' TO 'L:\FearlessDB_TaylorSwiftDB.ldf',
move 'TaylorSwiftDB' TO 'D:\FearlessDB_TaylorSwiftDB.mdf'
/*
Processed 66896 pages for database 'FearlessDB', file 'TaylorSwiftDB' on file 1.
Processed 2 pages for database 'FearlessDB', file 'TaylorSwiftDB_Log' on file 1.
RESTORE DATABASE successfully processed 66898 pages in 1.854 seconds (281.896 MB/sec).
-- SearchMe- use CTRL+F and search for this to find check MB/s for full restores
*/
-- ***********************************************************************************************************************************
-- * -- Differential Start -- *
-- ***********************************************************************************************************************************
restore database [FearlessDB] from
disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\DIFF\TSwift-SQL_TaylorSwiftDB_DIFF_20211022_164827_1.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\DIFF\TSwift-SQL_TaylorSwiftDB_DIFF_20211022_164827_2.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\DIFF\TSwift-SQL_TaylorSwiftDB_DIFF_20211022_164827_3.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\DIFF\TSwift-SQL_TaylorSwiftDB_DIFF_20211022_164827_4.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\DIFF\TSwift-SQL_TaylorSwiftDB_DIFF_20211022_164827_5.bak'
,disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\DIFF\TSwift-SQL_TaylorSwiftDB_DIFF_20211022_164827_6.bak'
with FILE = 1, NORECOVERY
/*
Processed 10432 pages for database 'FearlessDB', file 'TaylorSwiftDB' on file 1.
Processed 2 pages for database 'FearlessDB', file 'TaylorSwiftDB_Log' on file 1.
RESTORE DATABASE successfully processed 10434 pages in 1.281 seconds (63.631 MB/sec).
*/
-- ***********************************************************************************************************************************
-- * -- LOG Restores Start -- *
-- ***********************************************************************************************************************************
-- ***********************************************************************************************************************************
-- * Searching For compatible log files using the header info:
-- ***********************************************************************************************************************************
SET NOEXEC ON
-- ***********************************************************************************************************************************
restore headeronly from disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\LOG\TSwift-SQL_TaylorSwiftDB_LOG_20211022_165001.trn'
-- ***********************************************************************************************************************************
restore headeronly from disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\LOG\TSwift-SQL_TaylorSwiftDB_LOG_20211022_165653.trn'
/*
First Log LSN: 16995000000964000001
Full/Diff Last LSN: 16995000000964000001
Last Log LSN: 16995000000966400001
First compatible log file: \\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\LOG\TSwift-SQL_TaylorSwiftDB_LOG_20211022_165653.trn
*/
SET NOEXEC OFF
restore log [FearlessDB] from
disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\LOG\TSwift-SQL_TaylorSwiftDB_LOG_20211022_165653.trn'
with FILE = 1
, STOPAT = '2021-10-22 17:01:59.907'
/*
-- ***********************************************************************************************************************************
Processed 0 pages for database 'FearlessDB', file 'TaylorSwiftDB' on file 1.
Processed 2 pages for database 'FearlessDB', file 'TaylorSwiftDB_Log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 2 pages in 0.070 seconds (0.167 MB/sec).
*/
-- ***********************************************************************************************************************************
-- * Total Log file restore time: 2 seconds
-- ***********************************************************************************************************************************
restore log [FearlessDB] from
disk = '\\GsNetworkShare\G$\TSwift-SQL\TaylorSwiftDB\LOG\TSwift-SQL_TaylorSwiftDB_LOG_20211022_170000.trn'
with FILE = 1
, STOPAT = '2021-10-22 17:01:59.907'
/*
-- ***********************************************************************************************************************************
Processed 0 pages for database 'FearlessDB', file 'TaylorSwiftDB' on file 1.
Processed 2 pages for database 'FearlessDB', file 'TaylorSwiftDB_Log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 2 pages in 0.067 seconds (0.174 MB/sec).
*/
-- ***********************************************************************************************************************************
-- * Total Log file restore time: 1 seconds
-- ***********************************************************************************************************************************
SET NOEXEC OFF
-- ***********************************************************************************************************************************
-- * -- LOG Restores End -- *
-- ***********************************************************************************************************************************
-- ***********************************************************************************************************************************
-- * -- Put database in Recovery Mode/Online -- *
-- ***********************************************************************************************************************************
-- * Restore datetime: 2021-10-22 17:00:00.000
-- * Target restore datetime: 2021-10-22 17:01:59.907
-- * Offset allowance: 30 minutes.
-- * Difference (Target - Actual) : 1.98 minutes
-- ***********************************************************************************************************************************
if exists ( select *
from sys.databases d
where d.name = 'FearlessDB'
and [state] = 0
and d.is_in_standby = 1 )
or exists ( select *
from sys.databases d
where d.name = 'FearlessDB'
and d.[state] <> 0 )
restore database [FearlessDB] with recovery
/*
RESTORE DATABASE successfully processed 0 pages in 1.428 seconds (0.000 MB/sec).
*/
-- ***********************************************************************************************************************************
-- * -- Fix orphaned users-- *
-- ***********************************************************************************************************************************
exec master.dbo.FixOrphanedUsers 'FearlessDB'
--No orphaned users to fix.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- * -- Complete: Executed -- *
-- -----------------------------------------------------------------------------------------------------------------------------------
-- * Database: FearlessDB -- *
-- * Minutes: 0 -- *
-- * Seconds: 11 -- *
-- -----------------------------------------------------------------------------------------------------------------------------------
Parameters
Here’s every parameter, in English.
@database
Which database are we restoring? The name has to match the folder under @root_backup_directory, because that’s how Ola lays things out. Required. Get this wrong and you’re hunting ghosts.
@root_backup_directory
Where Ola dropped the backups. Local path or UNC, your call. The procedure looks here for the database folder, then FULL, DIFF, LOG. Required. If this path is wrong, nothing else matters.
@optional_restore_name
What to call the database when you’re done. Handy when you’re copying prod onto a test server and you don’t want two databases named the same thing screaming at each other. Pass something like TaylorSwiftDBReporting, or NULL to keep the original name.
@execute
Y prints the restore and runs it. N just prints. Required. If you have not stared at the output yet, use N. I mean it.
@new_server_data_drive_and_directory
Where the data files should land if this server doesn’t use the same drive letters as the source. No UNC paths. Leave AutoSelect if you want it to guess.
@new_server_log_drive_and_directory
Same idea, for the log file. Different drive letters between servers are a classic “why is my restore looking for F:\” moment. No UNC. Default is AutoSelect.
@intended_target_server
The only server this restore is allowed to touch. Hard code it. If the name doesn’t match, the SP should refuse to play. Required. This is the “I would like to keep my job” parameter.
@restore_datetime
Point in time you want. NULL means “whatever the newest backup set is when I hit go.” That’s the usual choice for a nightly restore test.
@recovery_method
What recovery model to leave behind: FULL, SIMPLE, BULK_LOGGED, or NULL to stop touching it.
@is_full_copy_only
Set Y if the full backup was COPY_ONLY. I don’t support COPY_ONLY logs or diffs here, and I only allow COPY_ONLY fulls so nobody gets clever. Default is N.
@is_shrink_log_after_restore
Set Y and it’ll shrink the log afterward, aiming for about 100 MB. Only happens if @execute = 'Y'. Great on a cramped test box. Not something I turn on in prod unless I like living dangerously. Default N.
@check_restore_time_integrity
Set Y and the procedure checks that the restored copy is actually as fresh as you think it is, using the offset knobs below. Only when you’re executing, not when you’re printing. If you passed a specific @restore_datetime, it checks that too. Default is Y, which is the paranoid setting, which is often the correct setting. Unless I guess if you’re restoring a really old backup on purpose.
@restore_offset_allowance_minutes
How stale the restored database is allowed to be, in minutes, when logs are in the restore path. Say prod takes log backups every 10 minutes and you set this to 15. Your reporting copy has to land within 15 minutes of prod or the SP throws a fit. I like running this daily (on a test server, relax) with @restore_datetime = NULL as a “are my backups even restorable”. Default is 30.
@change_restore_offset_for_simple_recovery_or_no_log_restores
The name is a novel, I know. This offset replaces @restore_offset_allowance_minutes when the database is SIMPLE, or when you turned logs off with @use_logs_for_restore_path = 'N'. You still need @check_restore_time_integrity = 'Y'. Default is 1440, aka one day, because SIMPLE backups are not exactly a stopwatch.
@use_logs_for_restore_path
Y puts transaction log backups in the restore path, which you want for a real point in time. N skips logs and finishes faster when you don’t care about them. Default Y.
@use_differentials_for_restore_path
Y uses differentials. Flip it to N if a diff is toast, you only want the latest full, you’re testing log restores, or some hero took a regular full outside the job (not COPY_ONLY) and wrecked the differential base. Skipping diffs can mean a pile of log restores. Default Y.
@is_clear_backup_history
Y wipes msdb backup and restore history when you’re done. LiteSpeed restores on a test box can get a lot snappier after that. It’s quite fascinating. Native restores usually shrug and notice zero benefit. Keep in mind, you’re nuking all of the msdb history. Which means you won’t have it. So if you need it (which you probably do, or would like to have, don’t do this). Why have this if it’s usually a bad idea? I like to live dangerously time to time. Default N.
@backup_software
Who wrote the backup files: NATIVE or LITESPEED. The restore commands change accordingly. Default is NATIVE. Redgate is not supported. I would support it if Redgate wants to give me a license on the house.
@change_db_owner_to_sa
Y makes sa the owner after restore. Default N. Use it if orphaned owners make you twitch.
@use_replace_for_restore
Y tacks REPLACE onto the restore. That’s fine when you’re restoring onto another server and you do not care about the tail of the log. Default is Y. If you do care about the tail of the log, maybe don’t restore over it?
@stats
RESTORE STATS, 1 to 100. 0 means omit STATS so the output isn’t a progress bar novel. LiteSpeed will take the syntax and then sometimes ignore it, which is a LiteSpeed thing. Default 0.
@check_db
Y runs DBCC CHECKDB after the restore. It is what you think it means. Slow and thorough while giving you a very great feeling or a very bad feeling when it finishes. Default N.
@enable_broker
Service Broker after restore: ENABLE_BROKER, ERROR_BROKER_CONVERSATIONS, or NEW_BROKER. NULL means leave broker alone, which is what I do unless I know the app needs it.
@drop_after_restore
Y restores the database and then drops it. Sounds rude. Perfect for “did last night’s Ola backups actually restore?” jobs where you don’t want leftover copies eating the disk. Bonus points for using this along with @check_db = 'Y'. Default N.
@standby_file_directory
Where to put a STANDBY undo file if you’re inching through logs. No UNC. NULL turns that off. This only generates STANDBY syntax when @execute = 'N'. Default NULL.
@is_create_snapshot
Y takes a snapshot after the restore. Default N.
@use_estimates_for_headeronly_metadata
Y skips trusting RESTORE HEADERONLY and guesses from file names instead. Faster. Less accurate. The restore can fail. Turn it on when HEADERONLY is taking a water break (TDE on big encrypted backups on some 2016/2017 builds). If HEADERONLY is behaving, leave this at N. Default N.
@continue_logs
It does not work, and it does not do anything. I started to code it, but then stopped for reasons. Leaving it here for the future.