Restoring a Database, The Easy Way

ByGarrett Geier

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. Syntax highlighting, tabs intact, plus a download link.

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 2020-06-04 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 = '2020-06-04 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'

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.

@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 some 2016 builds, big encrypted 2017 backups). 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.



Leave a Reply

Your email address will not be published. Required fields are marked *