use master go SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO if object_id('dbo.RestoreDatabase') is not null drop procedure dbo.RestoreDatabase go create procedure dbo.RestoreDatabase /* Description: This script will restore a specified database to any date/time. Author: GG */ -- The database name. This needs to match the folder's database name inside @root_backup_directory. -- Watch out for databases with spaces, it may have an issue if DatabaseBackup is outdated. Ola fixed this 9/2016 after I emailed him about it. @database varchar(128) -- The source of where the backups are located. ,@root_backup_directory varchar(4000) -- The name of the restored database (e.g. TaylorSwiftDB, EminemDB, or NULL to remain same) ,@optional_restore_name varchar(128) = null -- Print commmands or print/execute commands. (Y|N) ,@execute char(1) -- new drive/folder location. No UNC. -- This is useful when restoring across servers if the drive letters differ between the servers. ,@new_server_data_drive_and_directory varchar(4000) = 'AutoSelect' ,@new_server_log_drive_and_directory varchar(4000) = 'AutoSelect' -- Preferably hard-coded to prevent accidental restores and data loss. ,@intended_target_server varchar(128) -- point in time restore datetime. NULL can also be used to get most recent backup sets when -- this procedure is run. Otherwise an exact datetime can be used. ,@restore_datetime datetime = null -- the recovery model the restored database will be put into. ,@recovery_method varchar(11) = null -- FULL, SIMPLE, BULK_LOGGED, NULL to keep default -- Use this if the source backup was created with the COPY_ONLY option. -- no log/diff copy only supported at this time, Even though there's not a difference -- between a diff copy only and regular diff, just fulls are allowed to discourage weirdness. ,@is_full_copy_only char(1) = 'N' -- This will shrink the log file after the restore. Target size: 100 MB. -- Only enforced when @Execute = 'Y' ,@is_shrink_log_after_restore char(1) = 'N' -- this parameter will validate the actual restore time is within X minutes (@restore_offset_allowance_minutes) -- of the database restore time if the restore time was not specified (NULL). -- if the restore time is specified or @use_logs_for_restore_path = 'N', this check does not apply. -- if @restore_time IS NOT NULL, then this param will verify the restore time is as expected. -- Only enforced when @Execute = 'Y' ,@check_restore_time_integrity char(1) = 'Y' /* Explanation of @restore_offset_allowance_minutes: the restored database must be no more than X minutes old. This param only applies when @restore_datetime IS NOT NULL, @check_restore_time_integrity = 'Y', and there are log backups available. If @check_restore_time_integrity = 'N', then this does not apply. Example: Prod has backups transaction log backups running every 10 minutes. @restore_offset_allowance_minutes = 15. When restoring to Reporting, this param will check that after the restore is completed, that the DB will be a copy of production, no more than 10 minutes old. Since 10 minutes < 15 minutes, the operation will succeed. Another example: if the transaction log backups run every 10 minutes, and this param is SET = 15 [minutes] But there was an issue with the TLOG backups in prod, which caused them to not run. Since the restored database is more than 15 minutes old, tis SP will raise an error letting the caller know the database is off in the expected restore time. This can be used to help validate backups. For example, if this stored procedure is run daily on a test server with @restore_datetime = NULL and @restore_offset_allowance_minutes = 10, then we know that the backups are running as expected (and restorable) as long as the SP completes successfully. */ ,@restore_offset_allowance_minutes int = 30 -- If the database is in simple recovery or @use_logs_for_restore_path = 'N' then this value will be used -- instead of @restore_offset_allowance_minutes. -- This param will only apply to databases that are in simple recovery or if @use_logs_for_restore_path = 'N' -- Also, @check_restore_time_integrity must = 'Y' ,@change_restore_offset_for_simple_recovery_or_no_log_restores int = 1440 -- This option and the similar option below (@use_differentials_for_restore_path) are used to change the restore path. -- Typically this is set to "No" to speed up restores when the exact restore time is not necessary. ,@use_logs_for_restore_path char(1) = 'Y' -- Remove differentials from the restore path. This can be used if differential backups should not be evaluated -- in the restore (i.e. the diff backup file is corrupt, only the latest full backup is wanted, or to validate log backups). -- Another use case is if someone accidently took a full backup outside the normal backup process without using the COPY_ONLY option, -- all while deleting the full backup after it was used. The differential in this case would be dependent on the full backup. -- If this happens, only using the log backups (i.e. setting this to 'N') could get around this problem. -- if log files are used, and this option is selected, there will likely be considerably more log restores to go through. ,@use_differentials_for_restore_path char(1) = 'Y' -- Delete all data from the msdb restore/backup history tables. This is used for performance typically on non-prod servers. -- Litespeed restores will typically see large performance gains. Native restores, probably none. ,@is_clear_backup_history char(1) = 'N' -- Backup software used (LITESPEED, NATIVE) ,@backup_software varchar(128) = 'NATIVE' -- after the restore, change the owner to sa ,@change_db_owner_to_sa char(1) = 'N' -- add replace for the restore. If restoring to another server and/or don't care about the tail of the log, this should be fine. ,@use_replace_for_restore char(1) = 'Y' -- if stats are used, 1-100. otherwise 0 to omit them. FYI - the syntax is correct for LITESPEED, but it doesn't seem to work properly. ,@stats int = 0 -- run checkdb after restore ,@check_db char(1) = 'N' -- enable the service broker after restore. -- Valid options: ENABLE_BROKER, ERROR_BROKER_CONVERSATIONS, NEW_BROKER ,@enable_broker varchar(4000) = null -- after a successful restore, drop the database. ,@drop_after_restore char(1) = 'N' -- generate the syntax for the "with standby" (sometimes used for inching through the log files during the restore). -- this is where the standby file will be located. No UNC. Use NULL to turn off. -- @Execute must = 'N' ,@standby_file_directory varchar(4000) = null -- create a snapshot after the restore. ,@is_create_snapshot char(1) = 'N' -- use with caution, the metadata is not analyzed to check the output of RESTORE HEADERONLY. -- the restore may fail. -- Use this if RESTORE HEADERONLY runs very slow and you want a performance boost. -- 2017 encrypted backups on large databases can suffer from slow performance. -- this is not an issue with this stored procedure, it's an issue with RESTORE HEADERONLY. -- If RESTORE HEADERONLY goes very slow, then flip this to a 'Y'. -- There is a bug that exists in some versions of 2016 that can cause it to run very slow with TDE backups. -- (5+ hours instead of < 1 second). -- You typically want to use 'N' since it's more accurate than using file names to get the restore script. ,@use_estimates_for_headeronly_metadata char(1) = 'N' ,@continue_logs char(1) = 'N' as begin /* -- Example 1 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ Restore the TaylorSwiftDB database to a test environment. -- ~ Switch to SIMPLE Recovery; shrink the log file; Move data/log files to the new drives -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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 = N'X:\' ,@new_server_log_drive_and_directory = N'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 criteria as above, except do not execute, print SQL only. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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 restore. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ If TaylorSwiftDB in Reporting was dropped, this command would recover the database -- ~ and restore the database as the name: TaylorSwiftDBReporting. The restore time -- ~ would be exactly as of: 2020-06-04 14:25:06.467 -- ~ The recovery model is changed 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 = N'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' ------------------------------------------------------------------------------------------------------------------------------ -- Example 4 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Recovery: Simple -- Restore time: Latest possible backup, within 15 minutes of current time (getdate()). -- Verify restore datetime: Yes -- Clear backup history prior to restore: No. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ use master exec master.dbo.RestoreDatabase @database = 'TaylorSwiftDB' ,@root_backup_directory = '\\testserver\G$\testserver' ,@execute = 'Y' ,@new_server_data_drive_and_directory = 'X:\' ,@new_server_log_drive_and_directory = 'Y:\' ,@intended_target_server = N'test\test' ,@use_replace_for_restore = 'Y' ------------------------------------------------------------------------------------------------------------------------------ -- Example 5 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- A TaylorSwiftDB database table was dropped from testserver. -- The restore needs to be specifically as of: 2020-06-18 12:11:48.967. -- The latest backup will not be desirable. The SP call will be: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ exec master.dbo.RestoreDatabase @database = 'TaylorSwiftDB' ,@root_backup_directory = '\\testserver\G$\testserver' ,@optional_restore_name = NULL ,@execute = 'Y' ,@new_server_data_drive_and_directory = NULL ,@new_server_log_drive_and_directory = NULL ,@intended_target_server = 'testserver' ,@restore_datetime = '2020-06-18 12:11:48.967' ,@recovery_method = null--'FULL,SIMPLE,BULK_LOGGED' ,@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 = N'NATIVE' ,@use_replace_for_restore = 'Y' ,@check_db = 'N' ,@drop_after_restore = 'N' ,@standby_file_directory = NULL ,@is_create_snapshot = 'N' ,@use_estimates_for_headeronly_metadata = 'N' -- Example 6 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- This is the same as the example above, but the original database cannot -- be overwritten. It needs to be restored as a new database. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ exec master.dbo.RestoreDatabase @database = 'TaylorSwiftDB' ,@root_backup_directory = '\\testserver\G$\testserver' ,@optional_restore_name = 'TaylorSwiftDB_backup_20200618' ,@execute = 'Y' ,@new_server_data_drive_and_directory = 'E:\' ,@new_server_log_drive_and_directory = 'F:\' ,@intended_target_server = 'OKC_Server' ,@restore_datetime = '2020-06-18 12:11:48.967' ,@recovery_method = null--'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 = N'NATIVE' ,@use_replace_for_restore = 'Y' ,@check_db = 'N' ,@drop_after_restore = 'N' ,@standby_file_directory = NULL ,@is_create_snapshot = 'N' ,@use_estimates_for_headeronly_metadata = 'N' -- Example 7 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- The TaylorSwiftDB database needs to be restored to test. -- The backup needs to be the most current one available. Since the transaction log backups run -- every 10 minutes, we can confidently restore this database up to 10 minutes from CURRENT_TIMESTAMP. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ exec master.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 = N'test\test' ,@restore_datetime = null ,@recovery_method = 'SIMPLE' ,@is_full_copy_only = 'N' ,@is_shrink_log_after_restore = 'Y' ,@check_restore_time_integrity = 'Y' ,@use_logs_for_restore_path = 'Y' ,@use_differentials_for_restore_path = 'Y' ,@is_clear_backup_history = 'N' ,@backup_software = N'NATIVE' ,@use_replace_for_restore = 'Y' ,@check_db = 'N' ,@drop_after_restore = 'N' ,@standby_file_directory = NULL ,@is_create_snapshot = 'N' ,@use_estimates_for_headeronly_metadata = 'N' -- Example 8 /* Server: test\test Database: db2 Restore Time: Most recent backup */ exec master.dbo.RestoreDatabase @database = 'db2' ,@root_backup_directory = 'testserver_dir' ,@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 = '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 = N'NATIVE' ,@use_replace_for_restore = 'Y' ,@check_db = 'N' ,@drop_after_restore = 'N' ,@standby_file_directory = NULL ,@is_create_snapshot = 'N' ,@use_estimates_for_headeronly_metadata = 'N' */ -- the bug that is documented for 2012 also affects 2008R2. Refreshing the object explorer can -- cause deadlocks on the restore. Set the priority high on this process, so the object explorer -- will be chosen as the deadlock victim. The object explorer seems to auto retry on deadlock failures, so it's -- not apparent that anything happened to the victim. -- This can be replicated by restoring a database and mashing the refresh button on the object explorer, then -- taking a look at the deadlock details inside extended events. -- https://support.microsoft.com/en-us/kb/2725950 set deadlock_priority high; set nocount on; set transaction isolation level read uncommitted; declare @StartTime datetime = current_timestamp; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ create temporary proc for clearing history -- ~ Since this code is called multiple times, it's put in a temp proc instead of duplicating it -- ~ or doing some GOTO craziness. -- ~ Creating a persistent stored procedure for something like this is not desirable. -- ~ In Litespeed, clearing the history completely can give a huge performance gain with restores. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if exists (select * from sys.databases d with (nolock) where d.name = 'msdb' and d.state_desc = 'ONLINE' and @is_clear_backup_history = 'Y') begin exec (' if object_id(''tempdb..#print_ClearHistory'') is not null drop procedure #print_ClearHistory '); exec (' create procedure #print_ClearHistory as set nocount on raiserror('''',0,1) with nowait; raiserror('' -- ***********************************************************************************************************************************'',0,1) with nowait; raiserror('' -- ~ Delete Backup/Restore History'',0,1) with nowait -- ~ raiserror('' -- ***********************************************************************************************************************************'',0,1) with nowait; raiserror('' exec msdb.dbo.sp_delete_backuphistory @oldest_date = ''''9/9/2099'''''',0,1) with nowait; raiserror('''',0,1) with nowait; '); exec (' if object_id(''tempdb..#ClearHistory'') is not null drop procedure #ClearHistory '); exec (' create procedure #ClearHistory as set nocount on exec msdb.dbo.sp_delete_backuphistory @oldest_date = ''9/9/2099''; '); end declare @root_backup_directory_original_value varchar(255); set @root_backup_directory_original_value = @root_backup_directory; -- update the backup directories if "snippets" were used. -- these are just used for convienence. -- if ( @root_backup_directory in ('testserver_dir','OLTP_dir') ) set @root_backup_directory = '\\testserver.somedomain.com\BkupVol1\testserver'; else if ( @root_backup_directory in ('server2_dir','OLAP_dir') ) set @root_backup_directory = '\\server2.somedomain.com\J$\server2$server2\'; else if ( @root_backup_directory in ('test_dir')) set @root_backup_directory = '\\server.somedomain.com\directory\sql_backups\' + replace(@intended_target_server,'\','$') + '\' else if (@root_backup_directory like '%_|test[_]dir') set @root_backup_directory = '\\server.somedomain.com\directory\sql_backups\' + left(@root_backup_directory, charindex('|', @root_backup_directory) - 1); else if (@root_backup_directory = 'default_backup_dir' ) begin exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' ,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer' ,N'BackupDirectory' ,@root_backup_directory output; end; -- Qualify the path with a whack if it doesn't exist. i.e. "c:\temp" becomes "c:\temp\" -- This is used to standardize the directories. if right(@root_backup_directory,1) <> '\' and @root_backup_directory is not null set @root_backup_directory += '\'; declare @FullSourceDirectory varchar(4000) ,@DifferentialDirectorySourceLocation varchar(4000) ,@LogDirectorySourceLocation varchar(4000) ,@FullSourceDirectoryCopyOnly varchar(4000); select @FullSourceDirectory = @root_backup_directory + @database + '\FULL\' ,@DifferentialDirectorySourceLocation = @root_backup_directory + @database + '\DIFF\' ,@LogDirectorySourceLocation = @root_backup_directory + @database + '\LOG\' ,@FullSourceDirectoryCopyOnly = @root_backup_directory + @database + '\FULL_COPY_ONLY\'; -- Log initial information declare @Message varchar(max) = ''; declare @Info varchar(max) = ''; declare @break varchar(2) = ''; declare @char_10 varchar(11) = char(10); declare @stars varchar(200) = '-- ' + replicate('*',131); --To help make the output pretty. declare @dashes varchar(4000) = replace(@stars,'*','-'); raiserror('/*',0,1) with nowait; set @Info = 'Date and time: ' + convert(varchar(50),convert(datetime,@StartTime),121); raiserror(@Info,0,1) with nowait; set @Info = 'Server: ' + cast(serverproperty(N'ServerName') as varchar(255)); raiserror(@Info,0,1) with nowait; set @Info = 'Version: ' + cast(serverproperty(N'ProductVersion') as varchar(255)); raiserror(@Info,0,1) with nowait; set @Info = 'Edition: ' + cast(serverproperty('Edition') as varchar(255)); raiserror(@Info,0,1) with nowait; raiserror(@break,0,1) with nowait; set @Info = 'Parameters:'; raiserror(@Info,0,1) with nowait; set @Info = space(10) + ' @database = '+ coalesce('''' + replace(@database,'''','''''') + '''','NULL'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ' @root_backup_directory = '+ coalesce('''' + @root_backup_directory_original_value + '''','NULL'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ' @optional_restore_name = '+ coalesce('''' + @optional_restore_name + '''','NULL'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + '-- *------------------------------------------------------------'; raiserror(@Info,0,1) with nowait; set @Info = space(10) + '-- * - @restore_datetime = '+ coalesce(quotename(convert(varchar(50),@restore_datetime,121),''''),'NULL (latest backup available)'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + '-- *------------------------------------------------------------'; raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@intended_target_server = ' + quotename(coalesce(@intended_target_server,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@execute = ' + quotename(coalesce(@execute,'NULL'),''''); raiserror(@Info,0,1) with nowait; raiserror(@break,0,1) with nowait; set @Info = space(10) + '/************** Start: These values are computed from the @root_backup_directory *************************************/'; raiserror(@Info,0,1) with nowait; raiserror('',0,1) with nowait; set @Info = space(14) + ',@root_backup_directory' + space(14) + ' = ' + quotename(coalesce(@root_backup_directory,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(14) + ',@FullSourceDirectory' + space(16) + ' = ' + quotename(coalesce(@FullSourceDirectory,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(14) + ',@FullSourceDirectoryCopyOnly' + space(8) + ' = ' + quotename(coalesce(@FullSourceDirectoryCopyOnly,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(14) + ',@DifferentialDirectorySourceLocation' + space(0) + ' = ' + quotename(coalesce(@DifferentialDirectorySourceLocation,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(14) + ',@LogDirectorySourceLocation' + space(9) + ' = ' + quotename(coalesce(@LogDirectorySourceLocation,'NULL'),''''); raiserror(@Info,0,1) with nowait; raiserror('',0,1) with nowait; set @Info = space(10) + '/************* End: values are computed from the @root_backup_directory **********************************************/'; raiserror(@Info,0,1) with nowait; raiserror(@break,0,1) with nowait; declare @xtra_desc varchar(100) = case when @new_server_data_drive_and_directory = 'AutoSelect' then ' - (looking up data drive/folder from list stored inside this SP)' when @new_server_data_drive_and_directory is null then null when @new_server_data_drive_and_directory is not null then '' end; set @new_server_data_drive_and_directory += @xtra_desc declare @xtra_desc2 varchar(100) = case when @new_server_log_drive_and_directory = 'AutoSelect' then ' - (looking up log drive/folder from list stored inside this SP)' when @new_server_data_drive_and_directory is null then null when @new_server_data_drive_and_directory is not null then '' end; set @new_server_log_drive_and_directory += @xtra_desc2 set @Info = space(10) + ',@new_server_data_drive_and_directory = ' + coalesce('''' + @new_server_data_drive_and_directory + '''','NULL'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@new_server_log_drive_and_directory = ' + coalesce('''' + @new_server_log_drive_and_directory + '''', 'NULL'); raiserror(@Info,0,1) with nowait; raiserror(@break,0,1) with nowait; set @Info = space(10) + ',@recovery_method = ' + quotename(coalesce(@recovery_method,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@is_full_copy_only = ' + quotename(coalesce(@is_full_copy_only,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@is_shrink_log_after_restore = ' + quotename(coalesce(@is_shrink_log_after_restore,'NULL'),''''); raiserror(@Info,0,1) with nowait; raiserror('',0,1) with nowait; set @Info = space(10) + ',@check_restore_time_integrity = ' + quotename(coalesce(@check_restore_time_integrity,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@restore_offset_allowance_minutes = ' + coalesce(cast(@restore_offset_allowance_minutes as varchar(100)),'NULL'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@change_restore_offset_for_simple_recovery_or_no_log_restores = ' + coalesce(cast(@change_restore_offset_for_simple_recovery_or_no_log_restores as varchar(100)),'NULL'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@use_logs_for_restore_path = ' + quotename(coalesce(@use_logs_for_restore_path,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@use_differentials_for_restore_path = ' + quotename(coalesce(@use_differentials_for_restore_path,'NULL'),''''); raiserror(@Info,0,1) with nowait; raiserror('',0,1) with nowait; set @Info = space(10) + ',@is_clear_backup_history = ' + quotename(coalesce(@is_clear_backup_history,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@backup_software = ' + quotename(coalesce(@backup_software,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@change_db_owner_to_sa = ' + quotename(coalesce(@change_db_owner_to_sa,'NULL'),''''); raiserror(@Info,0,1) with nowait; raiserror('',0,1) with nowait; set @Info = space(10) + ',@use_replace_for_restore = ' + quotename(coalesce(@use_replace_for_restore,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@stats = ' + coalesce(cast(@stats as varchar(3)),'NULL'); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@check_db = ' + quotename(coalesce(@check_db,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@enable_broker = ' + quotename(coalesce(@enable_broker,'NULL'),''''); raiserror(@Info,0,1) with nowait; raiserror('',0,1) with nowait; set @Info = space(10) + ',@drop_after_restore = ' + quotename(coalesce(@drop_after_restore,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@standby_file_directory = ' + quotename(coalesce(@standby_file_directory,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@is_create_snapshot = ' + quotename(coalesce(@is_create_snapshot,'NULL'),''''); raiserror(@Info,0,1) with nowait; set @Info = space(10) + ',@use_estimates_for_headeronly_metadata = ' + quotename(coalesce(@use_estimates_for_headeronly_metadata,'NULL'),''''); raiserror(@Info,0,1) with nowait; raiserror('*/',10,1) with nowait; raiserror(@break,0,1) with nowait; -- if the same db is entered for the @optional_restore_name, then NULL it out. -- If they are different, then take the @optional_restore_name. -- If the restore name doesn't change, then this procedure only expects a single name. set @optional_restore_name = nullif(@optional_restore_name, @database); declare @target_database_name varchar(128) = isnull(@optional_restore_name,@database); -- if "AutoSelect" was used, then update the paths. -- in 2016+, the data/log drive can be taken from the server level setting -- older versions don't support this, so the servers/drives are hardcoded in this SP for convienence. if (@new_server_data_drive_and_directory like 'AutoSelect%') begin set @new_server_data_drive_and_directory = case when isnull(cast(serverproperty('InstanceDefaultDataPath') as varchar(128)),'') <> '' then cast(serverproperty('InstanceDefaultDataPath') as varchar(260)) when @intended_target_server = 'test' then 'X:\data\' when @intended_target_server = 'test2' then 'Y:\Data' else 'Unknown' end; end if (@new_server_log_drive_and_directory like 'AutoSelect%') begin set @new_server_log_drive_and_directory = case when isnull(cast(serverproperty('InstanceDefaultLogPath') as varchar(128)),'') <> '' then cast(serverproperty('InstanceDefaultLogPath') as varchar(260)) when @intended_target_server = 'test' then 'X:\Logs' when @intended_target_server = 'test2' then 'Y:\Logs' else 'Unknown' end; end --select -- new_server_data_drive_and_directory = @new_server_data_drive_and_directory -- ,new_server_log_drive_and_directory = @new_server_log_drive_and_directory -- Qualify the path with a whack if it doesn't exist. i.e. "c:\temp" becomes "c:\temp\" -- This is used to standardize the directories. if right(@new_server_data_drive_and_directory,1) <> '\' and @new_server_data_drive_and_directory is not null set @new_server_data_drive_and_directory += '\'; if right(@new_server_log_drive_and_directory,1) <> '\' and @new_server_log_drive_and_directory is not null set @new_server_log_drive_and_directory += '\'; --validating parameters if is_srvrolemember('sysadmin') <> 1 begin raiserror(N'User does not have sufficient rights. Don''t even try, bro.',16, 1) with nowait; return (1); end; if @database is null begin raiserror(N'Invalid value for @database, value cannot be NULL.', 16, 1) with nowait; return (1); end; if (@intended_target_server <> cast(serverproperty(N'ServerName') as varchar(128)) or @intended_target_server is null) begin raiserror(N'Invalid value for @intended_target_server. Are you sure you''re connected to the correct server? Did you forget to change the value of: @intended_target_server?', 16, 1) with nowait; return (1); end; if ( @execute not in ('N','Y') or @execute is null ) begin raiserror(N'Invalid value for @execute, Must be ''N'' or ''Y''.', 16, 1) with nowait; return (1); end; if (@recovery_method is not null and @recovery_method not in (N'simple','full','bulk_logged') ) begin raiserror('@recovery_method is invalid. Must be simple, full, or bulk_logged if specified',16,1) with nowait; return (1); end; if ( @is_full_copy_only not in ('N','Y') or @is_full_copy_only is null ) begin raiserror('@is_copy_only is invalid, it must be specified. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if ( @is_shrink_log_after_restore not in ('N','Y') or @is_shrink_log_after_restore is null ) begin raiserror('@is_shrink_log_after_restore is invalid. Log file shrinking is only supported in auto execute mode (@execute = ''Y'')', 16, 1) with nowait; return (1); end; if ( @check_restore_time_integrity not in ('N','Y') or @check_restore_time_integrity is null ) begin raiserror('@check_restore_time_integrity is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if (datediff(second,@restore_datetime,current_timestamp) <= 0) begin raiserror('@restore_time too recent. To get the latest possible backup, leave @restore_datetime = NULL. Or specify a valid datetime.',16,1) with nowait; return (1); end; if ( @use_logs_for_restore_path not in ('N','Y') or @use_logs_for_restore_path is null ) begin raiserror('@use_logs_for_restore_path is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if ( @use_differentials_for_restore_path not in ('N','Y') or @use_differentials_for_restore_path is null ) begin raiserror('@use_differentials_for_restore_path is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if ( @is_full_copy_only = 'Y' and @use_differentials_for_restore_path = 'Y' ) begin raiserror('For copy-only restores (@is_full_copy_only = ''Y''), no differentials are supported. ',16,1) with nowait; return (1); end; if ( @is_clear_backup_history not in ('N','Y') or @is_clear_backup_history is null ) begin raiserror('@is_clear_backup_history is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if ( @backup_software not in ('LITESPEED','NATIVE') or @backup_software is null ) begin raiserror('@backup_software is invalid. Currently, only LITESPEED and Native backups are supported.',16,1) with nowait; return (1); end; if (@backup_software = 'LITESPEED' and not exists (select * from master.sys.extended_procedures z where z.name = 'xp_restore_database') ) begin raiserror('Litespeed is not installed. It must be installed to restore user databases.',16,1) with nowait; return (1); end; if not exists (select * from master.sys.procedures p where p.name = 'FixOrphanedUsers') begin raiserror('Stored Procedure: dbo.FixOrphanedUsers is missing. It must be installed to resync the database user''s SIDS.',16,1) with nowait; return (1); end; if ( @change_db_owner_to_sa not in ('N','Y') or @change_db_owner_to_sa is null ) begin raiserror('@change_db_owner_to_sa is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if ( @use_replace_for_restore not in ('N','Y') or @use_replace_for_restore is null ) begin raiserror('@use_replace_for_restore is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; set @stats = isnull(@stats,0); if (@stats < 0 or @stats > 100) begin raiserror('@stats invalid. Valid values: 1-100. NULL or 0 to omit.',16,1) with nowait; return (1); end; if ( @check_db not in ('N','Y') or @check_db is null ) begin raiserror('@check_db is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if (@check_db = 'Y' and not exists (select * from master.sys.procedures p where p.name = 'DatabaseIntegrityCheck')) begin raiserror('Stored Procedure DatabaseIntegrityCheck does not exist. Install it (http://ola.hallengren.com) or change this parameter to ''N''',16,1) with nowait; return (1); end; if (@enable_broker = 'N') set @enable_broker = null; if ( @enable_broker is not null and (@enable_broker not in ('ENABLE_BROKER', 'ERROR_BROKER_CONVERSATIONS', 'NEW_BROKER')) ) begin raiserror('@enable_broker is invalid. If specified, valid options: ENABLE_BROKER, ERROR_BROKER_CONVERSATIONS, NEW_BROKER',16,1) with nowait; return (1); end; if ( @enable_broker is not null and @target_database_name in ('master','model') ) begin raiserror('Enabling the service broker on %s is not supported. @enable_broker must be NULL for master/model.',16,1,@target_database_name); return (1); end; if ( @drop_after_restore not in ('N','Y') or @drop_after_restore is null ) begin raiserror('@drop_after_restore is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if ( @is_create_snapshot not in ('N','Y') or @is_create_snapshot is null ) begin raiserror('@is_create_snapshot is invalid. Must be ''Y'' or ''N''.',16,1) with nowait; return (1); end; if ( @use_estimates_for_headeronly_metadata not in ('N','Y') or @use_estimates_for_headeronly_metadata is null ) begin raiserror('@use_estimates_for_headeronly_metadata is invalid. Must be ''Y'' or ''N''',16,1) with nowait; return (1); end; declare @ProductVersion varchar(20) = cast(serverproperty ('productversion') as varchar(20)); declare @MajorVersion smallint = cast(parsename(@ProductVersion, 4) as smallint); declare @MinorVersion smallint = cast(parsename(@ProductVersion, 3) as smallint); declare @BuildVersion smallint = cast(parsename(@ProductVersion, 2) as smallint); if ( @MajorVersion < 10 ) begin raiserror('SQL Server version prior to 2008 not supported.', 16, 1); return; end --select @ProductVersion, @MajorVersion, @MinorVersion, @BuildVersion declare @with_standby char(1) = 'N'; if (@standby_file_directory = 'N') set @standby_file_directory = null; if (@standby_file_directory is not null) set @with_standby = 'Y'; if (@with_standby = 'Y' and @execute = 'Y') begin raiserror('WITH STANDBY is currently only supported by printing the commands. @execute must = ''N'' It is only intended to help assist in creating the STANDBY syntax incase of a disaster when the dba must "inch through the logs" when a developer does something like ''DROP TABLE ImportantThings''',16,1) with nowait; return (1); end; declare @is_model bit = 0 ,@is_master bit = 0; -- Do not execute System Database restore commands, only generate if selected. if ( @target_database_name in ('model','msdb','master') ) begin set @execute = 'N'; raiserror('',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * Directly restoring System Databases are not supported.',0,1) with nowait; raiserror('-- * The restore statements will be generated, but the commands will not be executed.',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('',0,1) with nowait; if @database = 'master' set @is_master = 1; if @database = 'model' set @is_model = 1; end; -- unless the restore time was not set, make sure the restore time passed in -- is actually the time the database was restored as. declare @is_exact_restore_time_required bit = 1; declare @orig_restore_datetime datetime = @restore_datetime; if ( @restore_datetime is null ) begin set @restore_datetime = @StartTime; set @is_exact_restore_time_required = 0; end; declare @DrivesUsedByInstance table ( ID tinyint identity(1,1) ,DriveName nchar(1) ,MBFree int --only used for fixed drives. ); /* Get drives used by SQL instance for cluster installs. */ with CteClusteredSharedDrives (DriveName) as ( select DriveName from sys.dm_io_cluster_shared_drives ) insert @DrivesUsedByInstance (DriveName) select DriveName from CteClusteredSharedDrives; --not on a cluster, check the fixed drives if @@rowcount = 0 begin insert @DrivesUsedByInstance ( DriveName ,MBFree ) exec sys.xp_fixeddrives; end; declare @DirectoryExist table ( FileExists bit ,FileIsDirectory bit ,ParentDirectoryExists bit ); -- validate the drive/directory exists. If it is, then awesome + continue. Otherwise... :( -- Hooray for undocumented features! If for some reason m$ decides to remove/change -- this proc in a later version of SQL Server, it'll be dealt with then. It's working now, -- so it'll be left alone. if @new_server_data_drive_and_directory is not null if not exists (select * from @DrivesUsedByInstance d where d.DriveName = left(@new_server_data_drive_and_directory,1)) begin raiserror(N'Invalid drive specified for @new_server_data_drive_and_directory.',16, 1) with nowait; return (1); end; else begin insert @DirectoryExist ( FileExists ,FileIsDirectory ,ParentDirectoryExists ) exec sys.xp_fileexist @new_server_data_drive_and_directory; if not exists ( select * from @DirectoryExist where FileExists = 0 and FileIsDirectory = 1 and ParentDirectoryExists = 1 ) begin raiserror('Data directory does not exist. Check @new_server_data_drive_and_directory',16, 1) with nowait; return (1); end; end; if @new_server_log_drive_and_directory is not null if not exists (select * from @DrivesUsedByInstance d where d.DriveName = left(@new_server_log_drive_and_directory,1)) begin raiserror(N'Invalid drive specified for @new_server_log_drive_and_directory.',16, 1) with nowait; return (1); end; else begin delete @DirectoryExist; insert @DirectoryExist ( FileExists ,FileIsDirectory ,ParentDirectoryExists ) exec sys.xp_fileexist @new_server_log_drive_and_directory; if not exists ( select * from @DirectoryExist where FileExists = 0 and FileIsDirectory = 1 and ParentDirectoryExists = 1 ) begin raiserror('Log directory does not exist. Check @new_server_log_drive_and_directory.',16, 1) with nowait; return (1); end; end; if (@with_standby = 'Y') if not exists (select * from @DrivesUsedByInstance d where d.DriveName = left(@standby_file_directory,1)) begin raiserror(N'Invalid drive specified for @standby_file_directory.',16, 1) with nowait; return (1); end; else begin delete @DirectoryExist; insert @DirectoryExist ( FileExists ,FileIsDirectory ,ParentDirectoryExists ) exec sys.xp_fileexist @standby_file_directory; if not exists ( select * from @DirectoryExist where FileExists = 0 and FileIsDirectory = 1 and ParentDirectoryExists = 1 ) begin raiserror('Standby file directory does not exist. Check @standby_file_directory.',16, 1) with nowait; return (1); end; end; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ May need to find a better way to determine if a DB is in simple recovery. -- ~ This works most of the time. It will only not work as expected if the database is changed -- ~ from full --> simple recovery, and the left over, no longer needed LOG files/folders are left. -- ~ They need (and should be) to be deleted after the all the full backups associated with the log -- ~ backups are removed. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ if the database is in simple recovery, then there are no logs to restore. -- ~ No need to check the time integrity, because it won't ever be to the point in time. -- ~ we'll just give the backup (full/diff) that best matches the time specified. -- ~ The assumption is made that if no log backups exist, then the database is in simple recovery. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ declare @is_database_in_simple_recovery bit = 0; delete @DirectoryExist; insert @DirectoryExist ( FileExists ,FileIsDirectory ,ParentDirectoryExists ) exec sys.xp_fileexist @LogDirectorySourceLocation; if not exists ( select * from @DirectoryExist where FileExists = 0 and FileIsDirectory = 1 and ParentDirectoryExists = 1 ) begin set @is_database_in_simple_recovery = 1; end; /* Explanation of @restore_offset_allowance_minutes: the restored database must be no more than X minutes old. This param only applies when @restore_datetime IS NOT NULL, @check_restore_time_integrity = 'Y', and there are log backups available. If @check_restore_time_integrity = 'N', then this does not apply. Example: Prod has backups transaction log backups running every 10 minutes. @restore_offset_allowance_minutes = 15. When restoring to Reporting, this param will check that after the restore is completed, that the DB will be a copy of production, no more than 10 minutes old. Since 10 minutes < 15 minutes, the operation will succeed. Another example: if the transaction log backups run every 10 minutes, but this param is SET = 5 [minutes] this SP will raise an error letting the caller know the database is off in the expected restore time. this can be used to help validate backups. For example, if this stored procedure is run daily on a test server with @restore_datetime = NULL and @restore_offset_allowance_minutes = 10, then we know that the backups are running as expected as long as the SP completes successfully. */ if ( @orig_restore_datetime is null and @check_restore_time_integrity = 'Y' and @use_logs_for_restore_path = 'Y' ) if (@restore_offset_allowance_minutes <= 0 or @restore_offset_allowance_minutes > 35791393 or @restore_offset_allowance_minutes is null ) begin raiserror('@restore_offset_allowance_minutes is invalid. Must be ( >= 1 [minute] and < 35791393 [minutes]). If you don''t care, then set @check_restore_time_integrity = ''N''',16,1) with nowait; return (1); end; if ( @check_restore_time_integrity = 'Y' and (@is_database_in_simple_recovery = 1 or @use_logs_for_restore_path = 'N') ) begin if (@change_restore_offset_for_simple_recovery_or_no_log_restores <= 0 or @change_restore_offset_for_simple_recovery_or_no_log_restores > 35791393 or @change_restore_offset_for_simple_recovery_or_no_log_restores is null ) begin raiserror('@change_restore_offset_for_simple_recovery_or_no_log_restores is invalid. Must be ( >= 1 [minute] and < 35791393 [minutes]). If you don''t care, then set @check_restore_time_integrity = ''N''',16,1) with nowait; return (1); end; -- the database is in simple recovery, and we want to check the restore time integrity, this is -- done with @restore_offset_allowance_minutes set @restore_offset_allowance_minutes = @change_restore_offset_for_simple_recovery_or_no_log_restores end --/* beta remove if (@orig_restore_datetime is not null and @check_restore_time_integrity = 'Y' and @use_logs_for_restore_path = 'Y' and @is_database_in_simple_recovery = 0 and @restore_offset_allowance_minutes <> 0) begin -- the offset does not apply if @restore_datetime was passed in. set @restore_offset_allowance_minutes = 0; set @change_restore_offset_for_simple_recovery_or_no_log_restores = 0; raiserror(@stars, 0, 1) with nowait; raiserror('-- * -- INFORMATIONAL -- *',0,1) with nowait; raiserror(@dashes,0,1) with nowait; raiserror('-- * @restore_offset_allowance_minutes has been set = 0',0,1) with nowait; raiserror('-- * It does not apply when @restore_datetime is supplied.',0,1) with nowait; raiserror('-- * If @restore_datetime is supplied, then the database will be restored to exactly that point in time. ',0,1) with nowait; raiserror('-- * If there are exceptions to this, a message will be displayed explaining otherwise. ',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('',0,1) with nowait; end; ---- start debug ----select restore_offset_allowance_minutes = @restore_offset_allowance_minutes ----select change_restore_offset_for_simple_recovery_or_no_log_restores = @change_restore_offset_for_simple_recovery_or_no_log_restores ---- end debug -- shrink on the log file is only supported in "execute = Y" mode. -- in some cases the database doesn't exist, so the shrink SQL can't accurately be determined. if (@is_shrink_log_after_restore = 'Y' and @execute = 'N') begin set @is_shrink_log_after_restore = 'N'; raiserror(@stars,0,1) with nowait; raiserror('-- * -- INFORMATIONAL -- *',0,1) with nowait; raiserror(@dashes,0,1) with nowait; raiserror('-- * @is_shrink_log_after_restore has been changed to ''N''',0,1) with nowait; set @Message ='-- * The reason: @execute is set to ''N''. in some cases the database doesn''t exist, so the shrink SQL can''t accurately be determined.'; raiserror(@Message,0,1) with nowait; raiserror(@stars,10,1) with nowait; raiserror('',10,1) with nowait; end; /** Get all backups. After all full, differential, and log backups are stored, then they will be evaluated to see which ones are necessary and which ones aren't. This procedure attempts to generate the most efficient restore plan by default using a combination of full, differential, and log backups (based upon the values of the parameters). **/ if object_id('tempdb..#FullBackupFiles') is not null drop table #FullBackupFiles; if object_id('tempdb..#DifferentialBackupFiles') is not null drop table #DifferentialBackupFiles; if object_id('tempdb..#LogBackupFiles') is not null drop table #LogBackupFiles; -- The FileDateTime is a varchar, it will be converted to datetime when compared to a datetime -- because of data precedence. Since it can temporary contain a value that is not a date, a varchar is used -- instead of casting the field explicitly as a datetime. create table #FullBackupFiles ( Id int ,filename_only varchar(260) ,filename_and_directory varchar(260) ,depth smallint ,file_flag bit ,sequence_number varchar(260) ,filedatetime_varchar varchar(260) ,FileDateTimeDt datetime ,is_active_restore_file_family bit default (0) ,is_active_stripe_file bit default (0) ,StripFileOccurence int ,StripFileOccurence_varchar varchar(260) ,is_stripe_file_processed bit default(0) ); create table #DifferentialBackupFiles ( Id int ,filename_only varchar(260) ,filename_and_directory varchar(260) ,depth smallint ,file_flag bit ,sequence_number varchar(260) ,filedatetime_varchar varchar(260) ,FileDateTimeDt datetime ,is_active_restore_file_family bit default (0) ,is_active_stripe_file bit default (0) ,StripFileOccurence int ,StripFileOccurence_varchar varchar(260) ,is_stripe_file_processed bit default(0) ); create table #LogBackupFiles ( Id int ,filename_only varchar(260) ,filename_and_directory varchar(260) ,depth smallint ,file_flag bit ,sequence_number varchar(260) ,filedatetime_varchar varchar(260) ,FileDateTimeDt datetime ,IsProcessed bit default (0) ,IsDiscarded bit default (0) ,StartLSN numeric(25,0) ,LastLSN numeric(25,0) ,StripFileOccurence int ,StripFileOccurence_varchar varchar(260) ,is_stripe_file_processed bit default(0) ); declare @full_backup_directory varchar(260); if (@is_full_copy_only = 'N') set @full_backup_directory = @FullSourceDirectory; else set @full_backup_directory = @FullSourceDirectoryCopyOnly; insert #FullBackupFiles (filename_and_directory, depth, file_flag) exec master..xp_dirtree @full_backup_directory, 1, 1; if (@use_differentials_for_restore_path = 'Y') begin insert #DifferentialBackupFiles (filename_and_directory, depth, file_flag) exec master..xp_dirtree @DifferentialDirectorySourceLocation, 1, 1; end; -- same for logs. if (@use_logs_for_restore_path = 'Y') begin insert #LogBackupFiles (filename_and_directory, depth, file_flag) exec master..xp_dirtree @LogDirectorySourceLocation, 1, 1; end; -------------------------------------------------------------------------------------------------------------------------- -- FULL ------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------- declare @find_string varchar(4000) = 'FULL'; if (@is_full_copy_only = 'Y') begin set @find_string = 'FULL_COPY_ONLY'; end; update #FullBackupFiles set filename_only = substring(fbf.filename_and_directory,patindex('%' + @find_string + '%', fbf.filename_and_directory) + len(@find_string) + 1, 4000) from #FullBackupFiles fbf; update #FullBackupFiles set filename_and_directory = @full_backup_directory + filename_and_directory ,sequence_number = substring(filename_only,1,8) + ' ' + substring(filename_only,10,2) + ':' + substring(filename_only,12,2)+':' + substring(filename_only, 14, 2); -- if any files/folders exist that aren't created by ola's backup solution, remove them from this dataset. delete #FullBackupFiles where isdate(sequence_number) <> 1 update #FullBackupFiles set FileDateTimeDt = sequence_number; update #FullBackupFiles set filedatetime_varchar = convert(varchar(100),FileDateTimeDt,121); update #FullBackupFiles set StripFileOccurence_varchar = replace(stuff(filename_only,1,16,''),'.bak',''); update #FullBackupFiles set StripFileOccurence = case when isnumeric(StripFileOccurence_varchar) = 1 then StripFileOccurence_varchar else 1 end; -------------------------------------------------------------------------------------------------------------------------- -- DIFF ------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------- set @find_string = 'DIFF'; update #DifferentialBackupFiles set filename_only = substring( filename_and_directory,patindex('%' + @find_string + '%', filename_and_directory) + len(@find_string) + 1, 4000 ) from #DifferentialBackupFiles; update #DifferentialBackupFiles set filename_and_directory = @DifferentialDirectorySourceLocation + filename_and_directory ,sequence_number = substring(filename_only,1,8) + ' ' + substring(filename_only,10,2) + ':' + substring(filename_only,12,2)+':' + substring(filename_only, 14,2); -- if any files/folders exist that aren't created by ola's backup solution, remove them from this dataset. delete #DifferentialBackupFiles where isdate(sequence_number) <> 1 update #DifferentialBackupFiles set FileDateTimeDt = sequence_number; update #DifferentialBackupFiles set filedatetime_varchar = convert(varchar(100),FileDateTimeDt,121); update #DifferentialBackupFiles set StripFileOccurence_varchar = replace(stuff(filename_only,1,16,''),'.bak',''); update #DifferentialBackupFiles set StripFileOccurence = case when isnumeric(StripFileOccurence_varchar) = 1 then StripFileOccurence_varchar else 1 end; ------------------------------------------------------------------------------------------------------------------------------ -- LOGS ---------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------ set @find_string = 'LOG'; update #LogBackupFiles set filename_only = substring( filename_and_directory,patindex('%' + @find_string + '%', filename_and_directory) + len(@find_string) + 1, 4000 ) from #LogBackupFiles; update #LogBackupFiles set filename_and_directory = @LogDirectorySourceLocation + filename_and_directory ,sequence_number = substring(filename_only,1,8) + ' ' + substring(filename_only,10,2) + ':' + substring(filename_only,12,2)+':' + substring(filename_only, 14,2); -- if any files/folders exist that aren't created by ola's backup solution, remove them from this dataset. delete #LogBackupFiles where isdate(sequence_number) <> 1 update #LogBackupFiles set FileDateTimeDt = sequence_number; update #LogBackupFiles set filedatetime_varchar = replace(convert(varchar(100),FileDateTimeDt,121),'.bak',''); update #LogBackupFiles set StripFileOccurence_varchar = replace(stuff(filename_only,1,16,''),'.bak',''); update #LogBackupFiles set StripFileOccurence = case when isnumeric(StripFileOccurence_varchar) = 1 then StripFileOccurence_varchar else 1 end; /* --debug select * from #FullBackupFiles select * from #DifferentialBackupFiles select * from #LogBackupFiles return --*/ update #FullBackupFiles set FileDateTimeDt = cast(FileDateTime_varchar as datetime); update #DifferentialBackupFiles set FileDateTimeDt = cast(FileDateTime_varchar as datetime); update #LogBackupFiles set FileDateTimeDt = cast(FileDateTime_varchar as datetime); /*debug select * from #FullBackupFiles select * from #DifferentialBackupFiles select * from #LogBackupFiles return --*/ /* It may not be doable to use the most recent full backup for a point in time restore. It depends on the data that has been changed after backup pages are read, when they are read, active transactions, etc. There's no way of really knowing which log record transactions were rolled forward or rolled back. So to get a true point in time restore, the previous full backup will be needed. That way the STOPAT functionality can be used. Paul Randal has a couple great articles that go more indepth about how this process works: http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/ We will find out if a log file can be used for a point in time restore by using a combination of the backup_start and backup_finish times within the header metadata. */ delete #FullBackupFiles where FileDateTimeDt > @restore_datetime; update #FullBackupFiles set is_active_restore_file_family = 1 where FileDateTimeDt = (select max(FileDateTimeDt) from #FullBackupFiles z); -- check the full backup metadata to see if the @restore_datetime can be reached with a point-in-time restore -- using the backup. If the @restore_datetime > the backup_finish_datetime, then we're good. -- Otherwise, we will have to go to the previous full backup. -- Repeat for the differential backup (if it applies). declare @HeaderFile varchar(4000); declare @backup_finish_date datetime; declare @headerSql varchar(4000); if object_id('tempdb..#BackupHeader_Litespeed') is not null drop table #BackupHeader_Litespeed; create table #BackupHeader_Litespeed ( backup_header_id int identity(1,1) ,FileNumber int ,BackupFormat varchar(128) ,Guid uniqueidentifier ,BackupName varchar(128) ,BackupDescription varchar(128) ,BackupType varchar(128) ,ExpirationDate datetime ,Compressed tinyint ,position smallint ,DeviceType tinyint ,UserName varchar(128) ,servername varchar(128) ,DatabaseName varchar(128) ,DatabaseVersion int ,DatabaseCreationDate datetime ,BackupSize numeric(20,0) ,FirstLsn numeric(25,0) ,LastLsn numeric(25,0) ,CheckpointLsn numeric(25,0) ,DifferentialBaseLsn numeric(25,0) ,BackupStartDate datetime ,BackupFinishDate datetime ,SortOrder smallint ,CodePage smallint ,CompatibilityLevel tinyint ,SoftwareVendorId int ,SoftwareVersionMajor int ,SoftwareVersionMinor int ,SoftwareVersionBuild int ,MachineName varchar(128) ,BindingId uniqueidentifier ,RecoveryForkId uniqueidentifier ,encryption int ,IsCopyOnly varchar(128) ); if object_id('tempdb..#BackupHeader_native') is not null drop table #BackupHeader_native; --base cols for 2008. create table #BackupHeader_native ( backup_header_id int identity(1,1), BackupName varchar(128), BackupDescription varchar(255), BackupType smallint, ExpirationDate datetime, Compressed bit, Position smallint, DeviceType tinyint, UserName varchar(128), ServerName varchar(128), DatabaseName varchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20, 0), FirstLSN numeric(25, 0), LastLSN numeric(25, 0), CheckpointLSN numeric(25, 0), DatabaseBackupLSN numeric(25, 0), BackupStartDate datetime, BackupFinishDate datetime, SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int, SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName varchar(128), Flags int, BindingId uniqueidentifier, RecoveryForkId uniqueidentifier, Collation varchar(128), FamilyGUID uniqueidentifier, HasBulkLoggedData bit, IsSnapshot bit, IsReadOnly bit, IsSingleUser bit, HasBackupChecksums bit, IsDamaged bit, BeginsLogChain bit, HasIncompleteMetaData bit, IsForceOffline bit, IsCopyOnly bit, FirstRecoveryForkID uniqueidentifier, ForkPointLSN numeric(25, 0), RecoveryModel varchar(60), DifferentialBaseLSN numeric(25, 0), DifferentialBaseGUID uniqueidentifier, BackupTypeDescription varchar(60), BackupSetGUID uniqueidentifier, CompressedBackupSize bigint ); -- to consolidate header info from native/litespeed if object_id('tempdb..#BackupHeader') is not null drop table #BackupHeader; create table #BackupHeader ( backup_header_id int identity(1,1) ,FirstLSN numeric(25, 0) ,LastLSN numeric(25, 0) ,BackupFinishDate datetime ); --select @ProductVersion, @MajorVersion, @MinorVersion, @BuildVersion if @MajorVersion >= 11 begin alter table #BackupHeader_native add Containment tinyint; end if ( @MajorVersion >= 13 or ( @MajorVersion = 12 and @BuildVersion >= 2342 ) ) begin alter table #BackupHeader_native add KeyAlgorithm varchar(32); -- in litespeed, the datatype is varchar. the actual datatype, according to SQL Server, is varbinary. -- varchar will be used for compatibility. alter table #BackupHeader_native add EncryptorThumbprint varchar(4000); -- varbinary(20); alter table #BackupHeader_native add EncryptorType varchar(32); end /* select * from #BackupHeader bh select * from #BackupHeader_native bhn select * from #BackupHeader_Litespeed bhl return; --*/ if (@use_estimates_for_headeronly_metadata = 'Y') begin delete #FullBackupFiles where is_active_restore_file_family = 0 end else begin while exists ( select * from #FullBackupFiles ) begin truncate table #BackupHeader; truncate table #BackupHeader_Litespeed; truncate table #BackupHeader_native; set @HeaderFile = (select top (1) z.filename_and_directory from #FullBackupFiles z where z.is_active_restore_file_family = 1 order by z.StripFileOccurence ); if ( @backup_software = 'NATIVE' ) begin set @headerSql = 'RESTORE HEADERONLY FROM DISK = ''' + @HeaderFile + ''''; --print @headerSql begin try insert #BackupHeader_native exec (@headerSql); end try begin catch raiserror('error reading meta data for: %s',0,1) with nowait; raiserror('',0,1) with nowait; end catch end; else if (@backup_software = 'LITESPEED') begin set @headerSql = 'exec master.dbo.xp_restore_headeronly @filename = ''' + @HeaderFile + ''''; -- Litespeed can be used with native SQL Backups. If a native format is used, use the native restore #temp table. -- assume an error is because a native SQL Backup is used. begin try insert #BackupHeader_Litespeed exec (@headerSql); end try begin catch insert #BackupHeader_native exec (@headerSql); end catch; if not exists ( select * from #BackupHeader_Litespeed ) and not exists ( select * from #BackupHeader_native ) begin raiserror('Error in master.dbo.xp_restore_headeronly.', 16, 1); return (1); end; end; insert #BackupHeader ( LastLSN ,BackupFinishDate ) select b.LastLsn ,b.BackupFinishDate from #BackupHeader_Litespeed b union all select b.LastLSN ,b.BackupFinishDate from #BackupHeader_native b; set @backup_finish_date = (select z.BackupFinishDate from #BackupHeader z); if ( @restore_datetime > @backup_finish_date ) begin -- we're good, keep only the file we just checked delete #FullBackupFiles where is_active_restore_file_family <> 1; break; end; else begin -- check the next one delete #FullBackupFiles where is_active_restore_file_family = 1; update #FullBackupFiles set is_active_restore_file_family = 1 where FileDateTimeDt = (select max(FileDateTimeDt) from #FullBackupFiles z); end; end; end --do the same for DIFF backups delete #DifferentialBackupFiles where FileDateTimeDt >= @restore_datetime; -- there will be multiple of the same value if file striping is used. delete #DifferentialBackupFiles where FileDateTimeDt < (select distinct FileDateTimeDt from #FullBackupFiles); update #DifferentialBackupFiles set is_active_restore_file_family = 1 where FileDateTimeDt = (select max(FileDateTimeDt) from #DifferentialBackupFiles z); if (@use_estimates_for_headeronly_metadata = 'Y') begin delete #DifferentialBackupFiles where is_active_restore_file_family = 0 end else begin while exists ( select * from #DifferentialBackupFiles ) begin truncate table #BackupHeader; truncate table #BackupHeader_Litespeed; truncate table #BackupHeader_native; set @HeaderFile = null; set @backup_finish_date = null; set @HeaderFile = (select top (1) z.filename_and_directory from #DifferentialBackupFiles z where z.is_active_restore_file_family = 1 order by z.StripFileOccurence ); if ( @backup_software = 'NATIVE' ) begin set @headerSql = 'RESTORE HEADERONLY FROM DISK = ''' + @HeaderFile + ''''; insert #BackupHeader_native exec (@headerSql); end; else if (@backup_software = 'LITESPEED') begin set @headerSql = 'exec master.dbo.xp_restore_headeronly @filename = ''' + @HeaderFile + ''''; -- Litespeed can be used with native SQL Backups. If a native format is used, use the native restore #temp table. -- assume an error is because a native SQL Backup is used. begin try insert #BackupHeader_Litespeed exec (@headerSql); end try begin catch insert #BackupHeader_native exec (@headerSql); end catch; if not exists ( select * from #BackupHeader_Litespeed ) and not exists ( select * from #BackupHeader_native ) begin raiserror('Error in master.dbo.xp_restore_headeronly.', 16, 1); return (1); end; end; insert #BackupHeader ( LastLSN ,BackupFinishDate ) select b.LastLsn ,b.BackupFinishDate from #BackupHeader_Litespeed b union all select b.LastLSN ,b.BackupFinishDate from #BackupHeader_native b; if not exists (select * from #BackupHeader) begin raiserror('Error populating backup header info.', 16, 1); return (1); end; set @backup_finish_date = (select z.BackupFinishDate from #BackupHeader z); -- TODO: may want to change to use the LSN instead of the backup_finish_date. if ( @restore_datetime >= @backup_finish_date ) begin -- we're good, keep only the file we just checked delete #DifferentialBackupFiles where is_active_restore_file_family = 0; break; end; else begin -- check the next one delete #DifferentialBackupFiles where is_active_restore_file_family = 1; update #DifferentialBackupFiles set is_active_restore_file_family = 1 where FileDateTimeDt = (select max(FileDateTimeDt) from #DifferentialBackupFiles z); end; end; end --Remove logs that cannot be valid for this restore path. delete #LogBackupFiles where FileDateTimeDt <= (select distinct FileDateTimeDt from #FullBackupFiles); -- remove the logs that aren't needed because they are included within the DIFF backup. if exists (select * from #DifferentialBackupFiles ) begin delete #LogBackupFiles where FileDateTimeDt <= (select distinct FileDateTimeDt from #DifferentialBackupFiles); end -- create sequential IDS. Used primarily for debugging. ;with CteGetIds as ( select *, RowId = row_number() over (order by FileDateTimeDt) from #FullBackupFiles ) update CteGetIds set Id = RowId ;with CteGetIds as ( select *, RowId = row_number() over (order by FileDateTimeDt) from #DifferentialBackupFiles ) update CteGetIds set Id = RowId ;with CteGetIds as ( select *, RowId = row_number() over (order by FileDateTimeDt) from #LogBackupFiles ) update CteGetIds set Id = RowId; /* select * from #FullBackupFiles select * from #DifferentialBackupFiles select * from #LogBackupFiles return --*/ --set differential and full backup filenames declare @FullBackupFileName varchar(260); declare @DiffBackupFileName varchar(260); --start off with the first file. If it's not a stripe set then the stripe number will be 1. update #FullBackupFiles set is_active_stripe_file = 1 where is_active_restore_file_family = 1 and StripFileOccurence = 1; set @FullBackupFileName = ( select fbf.filename_and_directory from #FullBackupFiles fbf where fbf.is_active_restore_file_family = 1 and fbf.StripFileOccurence = 1 ); update #DifferentialBackupFiles set is_active_stripe_file = 1 where is_active_restore_file_family = 1 and StripFileOccurence = 1; set @DiffBackupFileName = ( select z.filename_and_directory from #DifferentialBackupFiles z where z.is_active_restore_file_family = 1 and z.StripFileOccurence = 1 ); declare @restore_datetime_varchar varchar(50) = convert(varchar(50),@restore_datetime,121); --we didn't find anything. if ( @FullBackupFileName is null ) begin declare @full_backup_not_exist_message varchar(1000); set @full_backup_not_exist_message = 'The full backup does not exist, the source database is spelled wrong, or @root_backup_directory is incorrect.' + @char_10; set @full_backup_not_exist_message += 'Usually @root_backup_directory isn''t correct. Double check them:' + @char_10; set @full_backup_not_exist_message += '@root_backup_directory = %s' + @char_10; set @full_backup_not_exist_message += '@database = %s' + @char_10 + @char_10; set @full_backup_not_exist_message += 'Other possible cause: is that the backup files that comply with @restore_datetime do not exist.' + @char_10; set @full_backup_not_exist_message += '(i.e. restore datetime is 12:01 AM today, but the latest backup is from 2 days ago) ' + @char_10; set @full_backup_not_exist_message += '@restore_datetime = %s' + @char_10 + @char_10; set @full_backup_not_exist_message += 'Enter the full root path to the database that was backed up using DatabaseBackup' + @char_10; set @full_backup_not_exist_message += '(http://ola.hallengren.com - not including the database name, but including the server name)' + @char_10; set @full_backup_not_exist_message += 'For example: \\uncPath\C$\Server$Server | \\machineName\X$\MyServer | c:\MyBackupDir | NULL or use_default_backup_directory to use the default backup directory).'; raiserror(@full_backup_not_exist_message, 16, 1, @root_backup_directory, @database,@restore_datetime_varchar); return (1); end; /* debug select * from #FullBackupFiles fbf select * from #DifferentialBackupFiles dbf select * from #LogBackupFiles lbf return --*/ -- all the datatypes are actually varchar(4000) for xp_restore_filelistonly -- but we'll be a rebel and use these data types instead. -- See: https://documents.software.dell.com/litespeed-for-sql-server/8.2/user-guide/use-extended-stored-procedures/xp_restore_filelistonly if object_id('tempdb..#FileList_litespeed') is not null drop table #FileList_litespeed; create table #FileList_Litespeed ( ID int identity(1,1) primary key clustered ,LogicalName varchar(128) ,PhysicalName varchar(260) ,Type char(1) ,FileGroupName varchar(128) ,Size bigint ,MaxSize bigint ,FileId int ,BackupSizeInBytes bigint ,FileGroupID int ); if object_id('tempdb..#FileList_native') is not null drop table #FileList_native; create table #FileList_native ( LogicalName varchar(128), PhysicalName varchar(260), Type char(1), FileGroupName varchar(128), Size numeric(20,0), MaxSize numeric(20,0), FileID bigint, CreateLSN numeric(25,0), DropLSN numeric(25,0), UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0), ReadWriteLSN numeric(25,0), BackupSizeInBytes bigint, SourceBlockSize int, FileGroupID int, LogGroupGUID uniqueidentifier, DifferentialBaseLSN numeric(25,0), DifferentialBaseGUID uniqueidentifier, IsReadOnl bit, IsPresent bit, TDEThumbprint varchar(4000) ); --these are the fields we actually care about. Derived from the results from #FileList_litespeed && #FileList_native if object_id('tempdb..#FileList') is not null drop table #FileList; create table #FileList ( ID int identity(1,1) primary key clustered ,LogicalName varchar(128) ,PhysicalName varchar(260) ,Type char(1) ,FileGroupID int ); if @MajorVersion >= 13 begin alter table #FileList_native add SnapshotURL varchar(360); end declare @fileListOnlySql varchar(4000); if (@new_server_data_drive_and_directory is not null and @new_server_log_drive_and_directory is not null) begin if (@backup_software = 'NATIVE') begin set @fileListOnlySql = 'restore filelistonly from disk = ''' + @FullBackupFileName + ''''; insert #FileList_native exec (@fileListOnlySql); end; else if (@backup_software = 'LITESPEED') begin set @fileListOnlySql = 'exec master.dbo.xp_restore_filelistonly @filename = ''' + @FullBackupFileName + ''''; -- Litespeed can be used with native SQL Backups. If a native format is used, use the native restore #temp table. -- assume an error is because a native SQL Backup is used. begin try insert #FileList_Litespeed exec (@fileListOnlySql); end try begin catch insert #FileList_native exec (@fileListOnlySql); end catch; end; insert #FileList ( LogicalName ,PhysicalName ,Type ,FileGroupID) select f.LogicalName ,f.PhysicalName ,f.Type ,f.FileGroupID from #FileList_Litespeed f union all select f.LogicalName ,f.PhysicalName ,f.Type ,f.FileGroupID from #FileList_native f; if not exists (select * from #FileList) begin raiserror('Error in "restore filelistonly" check @FullBackupFileName.',16,1); return(1); end; -- a new table will make things easier. Modifying temp tables is messy, and intellisense hates it. select * ,IsDone = 0 ,PhysicalName_formatted = cast(null as varchar(max)) ,FileMoveStmt = cast(null as varchar(max)) into #FileList_PhysicalNames from #FileList; declare @ID int ,@complilation_of_move_commands varchar(max) = '' ,@data_move_stmt varchar(max) ,@moveCmd_temp varchar(max) = '' ,@IsDisplayRestoreStmtAsCSV bit = 0 ,@LogicalName varchar(max) ,@PhysicalFileName varchar(max) ,@BackupType char(1) ,@move_file_and_path varchar(max) ,@logical_names_for_snapshot varchar(max); if ( @is_master = 0 and @is_model = 0 ) begin while exists (select * from #FileList_PhysicalNames z where z.IsDone = 0) begin select top (1) @LogicalName = z.LogicalName ,@PhysicalFileName = reverse(substring(reverse(PhysicalName),1,charindex('\',reverse(PhysicalName))-1)) ,@ID = z.ID ,@BackupType = z.Type from #FileList_PhysicalNames z where z.IsDone = 0 order by z.FileGroupID asc; -- create move statement ("WITH MOVE") set @move_file_and_path = case when @BackupType = 'D' then @new_server_data_drive_and_directory else @new_server_log_drive_and_directory end + isnull(@optional_restore_name + '_','') + @PhysicalFileName; if (@backup_software = 'LITESPEED') begin set @data_move_stmt = cast('' as varchar(max)) + ' move ' + '''''' + @LogicalName + '''''' + ' TO ' + '''''' + @move_file_and_path + ''''''; set @moveCmd_temp = cast('' as varchar(max)) + ',@with = ' + '''' + @data_move_stmt + '''' + char(13); end; else if (@backup_software = 'NATIVE') begin set @data_move_stmt = cast('' as varchar(max)) + ' move '+ '''' + @LogicalName + '''' + ' TO ' + '''' + @move_file_and_path + '''' + ','; set @moveCmd_temp = @data_move_stmt + char(13); end; set @complilation_of_move_commands += @moveCmd_temp; update #FileList_PhysicalNames set IsDone = 1 ,PhysicalName_formatted = @PhysicalFileName ,FileMoveStmt = @moveCmd_temp from #FileList_PhysicalNames where ID = @ID; select @ID = null ,@moveCmd_temp = null ,@data_move_stmt = null ,@BackupType = null; set @complilation_of_move_commands = isnull(@complilation_of_move_commands,''); if ( len(@complilation_of_move_commands) = 0 ) begin raiserror('There was an issue generating the MOVE statements. :(',16,1) with nowait; return (1); end; else if (len(@complilation_of_move_commands) > 3500 and @IsDisplayRestoreStmtAsCSV = 0 ) begin set @IsDisplayRestoreStmtAsCSV = 1; raiserror('INFORMATIONAL: The commands are too large to display here. If you wish to view them anyway',0,1) with nowait; raiserror('the SELECT returned by this procedure can be saved as a CSV from within SSMS.', 0,1) with nowait; raiserror('The CSV will have all of the data for the generated statements.', 0,1) with nowait; raiserror('Right click the cell within SSMS, then choose "Save as CSV", then open in a text editor.', 0,1) with nowait; raiserror('That is, if you really, really want to view the commands.', 0,1) with nowait; end; end; end; end; --remove last comma if it exists. It will be there if it's a native restore. Somewhat messy, but it works. if ( @backup_software = 'NATIVE' and right(@complilation_of_move_commands,2) = ',' + char(13) ) begin set @complilation_of_move_commands = left(@complilation_of_move_commands,len(@complilation_of_move_commands) - 2 ); end; raiserror('',0,1) with nowait; raiserror('use master;',0,1) with nowait; raiserror('set nocount on;',0,1) with nowait; raiserror('set deadlock_priority high;',0,1) with nowait; raiserror('set transaction isolation level read uncommitted;',0,1) with nowait; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ drop all database snapshots if they exist. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if object_id('tempdb..#snapshots') is not null drop table #snapshots; select snapshot_name = d.name ,snapshot_database_id = d.database_id ,is_done = 0 into #snapshots from sys.databases d where source_database_id = db_id(@target_database_name); if (@@rowcount > 0) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * Dropping Database Snapshots',0,1) with nowait; raiserror(@stars,0,1) with nowait; end; declare @snapshot_to_drop varchar(128) ,@sql_snapshot_drop varchar(4000); while exists (select * from #snapshots z where z.is_done = 0) begin set @sql_snapshot_drop = ''; select top (1) @snapshot_to_drop = snapshot_name from #snapshots z where is_done = 0; set @sql_snapshot_drop = ' drop database ' + quotename(@snapshot_to_drop) + ';'; raiserror(@sql_snapshot_drop,0,1) with nowait; if (@execute = 'Y') begin exec (@sql_snapshot_drop); end; update #snapshots set is_done = 1 where snapshot_name = @snapshot_to_drop; end; /* First, take the database offline so the restore can happen without anyone interferring */ declare @offlineSql varchar(max); declare @does_database_exist bit = 0; if exists (select * from sys.databases where name = @target_database_name) begin set @does_database_exist = 1; --Is the database in a recovered state? If so, set offline (only if not system databases) if exists (select * from sys.databases where name = @target_database_name and state = 0 and database_id >= 5) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * -- Set database Offline -- *'; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @offlineSql = ' alter database ' + quotename(@target_database_name); set @offlineSql += ' set offline with rollback immediate'; raiserror(@offlineSql,0,1) with nowait; if @execute = 'Y' begin exec (@offlineSql); end; raiserror(@break,0,1) with nowait; end; end; /* Build SQL statement for full database restore. */ raiserror(@break,0,1) with nowait; -- print the clear history commands if specified. -- These will not be run unless @Execute = 'Y' (actual execute stmts are below) if (@is_clear_backup_history = 'Y') begin exec #print_ClearHistory; end; raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * -- Full Restore -- *',0,1) with nowait; raiserror(@stars,0,1) with nowait; declare @RestoreDatabaseSqlCmd varchar(max) = ''; declare @IsFirstLoopIteration bit = 1; declare @standby_command varchar(4000); if (@backup_software = 'LITESPEED') begin set @Message = ''; set @RestoreDatabaseSqlCmd = ''; set @Message = 'declare @return_code2 int; '; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = 'exec @return_code2 = master.dbo.xp_restore_database'; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = ' @database = ' + '''' + @target_database_name + ''''; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; -- add in all of the backup files if they are striped while exists ( select * from #FullBackupFiles z where z.is_stripe_file_processed = 0) begin set @FullBackupFileName = (select top (1) z.filename_and_directory from #FullBackupFiles z where z.is_active_restore_file_family = 1 and z.is_stripe_file_processed = 0 order by z.StripFileOccurence); set @Message = ',@filename = ' + '''' + @FullBackupFileName + ''''; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; update #FullBackupFiles set is_stripe_file_processed = 1 where filename_and_directory = @FullBackupFileName; end; set @Message = ',@filenumber = 1 '; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; if (@with_standby = 'N') begin set @Message = ',@with = ''NORECOVERY'''; end; else begin set @standby_file_directory += 'standby_undo_' + @target_database_name + '.bak'; set @Message = ',@with = ''STANDBY = ''''' + @standby_file_directory + ''''''''; end; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; if (@use_replace_for_restore = 'Y') begin set @Message = ',@with = REPLACE'; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; if (@stats > 0) begin set @Message = ',@with = ''STATS = ' + cast(@stats as varchar(3)) + ''''; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; if ( len(@complilation_of_move_commands) > 0 ) begin set @Message = @complilation_of_move_commands; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; set @Message = space(5) + 'if @return_code2 <> 0 begin raiserror(''Error in xp_restore_database.'',16,1) end'; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; else if (@backup_software = 'NATIVE') begin set @Message = ''; set @RestoreDatabaseSqlCmd = ''; set @Message = 'restore database ' + quotename(@target_database_name) + ' from'; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; -- add in all of the backup files if they are striped while exists ( select * from #FullBackupFiles z where z.is_stripe_file_processed = 0) begin set @FullBackupFileName = (select top (1) z.filename_and_directory from #FullBackupFiles z where z.is_active_restore_file_family = 1 and z.is_stripe_file_processed = 0 order by z.StripFileOccurence); -- essentially, add the comma if it needs to be there. if ( @IsFirstLoopIteration = 1 ) begin set @Message = ' disk = '''; end; else begin set @Message = ',disk = '''; end; set @Message += @FullBackupFileName + ''''; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; update #FullBackupFiles set is_stripe_file_processed = 1 where filename_and_directory = @FullBackupFileName; set @IsFirstLoopIteration = 0; end; set @Message = ' with FILE = 1'; if (@use_replace_for_restore = 'Y') begin set @Message += ', REPLACE'; end; if ( @with_standby = 'N' ) begin set @Message += ', NORECOVERY'; end; else begin set @standby_file_directory += 'standby_undo_' + @target_database_name + '.bak'; set @standby_command = ', STANDBY = ''' + @standby_file_directory + ''''; set @Message += @standby_command; end; if (@stats > 0) begin set @Message += ', STATS = ' + cast(@stats as varchar(3)); end; if ( len(@complilation_of_move_commands) > 0 ) begin set @Message += ','; end; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; if ( len(@complilation_of_move_commands) > 0 ) begin set @Message = @complilation_of_move_commands; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; end; -- display restore statement inside select grid. if (@IsDisplayRestoreStmtAsCSV = 1) begin select RightClickAndSaveThisAsCSV = @RestoreDatabaseSqlCmd end; if @execute = 'Y' begin -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ Delete the backup/restore history if specified -- ~ Why do this? Performance. I know, I know, this seems really strange. It speeds up performance (namely in Litespeed) -- ~ because SQL Server apparently scans the msdb tables during backup/restore operations. Use this if you're desperate to squeeze -- ~ every last bit of performance and don't care if msdb to emptied. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if (@is_clear_backup_history = 'Y') begin raiserror(@break,0,1) with nowait; raiserror(@break,0,1) with nowait; exec #ClearHistory; raiserror(@break,0,1) with nowait; end; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ Try to make the output messages a little more readable, especially if it was copied into a new window. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ raiserror(@break,0,1) with nowait; raiserror('/*',0,1); -- with wait. :) exec (@RestoreDatabaseSqlCmd); -- finish commenting out the output raiserror('-- SearchMe- use CTRL+F and search for this to find check MB/s for full restores', 0, 1) with nowait raiserror('*/',0,1) with nowait; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ Find out if the restore is actually happening and didn't error. -- ~ Using an output variable and sp_executesql could be used, but instead, we'll -- ~ just query sys.databases and see if the db is in a restoring state. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if not exists (select * from sys.databases z where z.name = @target_database_name and z.state = 1) begin raiserror('The database is not in a restoring state, it should be. Check above errors.',16,1) with nowait; return (1); end; end; /* Build SQL statement for differential database restore. */ -- add in all of the diff backup files if @DiffBackupFileName is not null begin set @RestoreDatabaseSqlCmd = ''; set @Message = ''; if (@is_clear_backup_history = 'Y') begin exec #print_ClearHistory; end; raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * -- Differential Start -- *'; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@break,0,1) with nowait; if (@backup_software = 'LITESPEED') begin set @Message = ' declare @return_code1 int; '; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = 'exec @return_code1 = master.dbo.xp_restore_database '; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = ' @database = ' + '''' + @target_database_name + ''''; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; -- add in all of the backup files if they are striped while exists ( select * from #DifferentialBackupFiles z where z.is_stripe_file_processed = 0) begin set @DiffBackupFileName = (select top (1) z.filename_and_directory from #DifferentialBackupFiles z where z.is_active_restore_file_family = 1 and z.is_stripe_file_processed = 0 order by z.StripFileOccurence); set @Message = ',@filename = ' + '''' + @DiffBackupFileName + ''''; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; update #DifferentialBackupFiles set is_stripe_file_processed = 1 where filename_and_directory = @DiffBackupFileName; end; set @Message = ',@filenumber = 1 '; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; if (@with_standby = 'Y') begin set @Message = ',@with = ''STANDBY = ''''' + @standby_file_directory + ''''''''; end; else begin set @Message = ',@with = ''NORECOVERY'''; end; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = space(5) + 'if @return_code1 <> 0 begin raiserror(''Error in xp_restore_database.'',16,1) end '; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; else if (@backup_software = 'NATIVE') begin set @Message = ''; set @RestoreDatabaseSqlCmd = ''; set @IsFirstLoopIteration = 1; set @Message = 'restore database ' + quotename(@target_database_name) + ' from'; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; -- add in all of the backup files if they are striped while exists ( select * from #DifferentialBackupFiles z where z.is_stripe_file_processed = 0) begin set @DiffBackupFileName = (select top (1) z.filename_and_directory from #DifferentialBackupFiles z where z.is_active_restore_file_family = 1 and z.is_stripe_file_processed = 0 order by z.StripFileOccurence); -- essentially, add the comma if it needs to be there. if ( @IsFirstLoopIteration = 1 ) begin set @Message = ' disk = '''; end; else begin set @Message = ',disk = '''; end; set @Message += @DiffBackupFileName + ''''; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; update #DifferentialBackupFiles set is_stripe_file_processed = 1 where filename_and_directory = @DiffBackupFileName; set @IsFirstLoopIteration = 0; end; set @Message = ' with FILE = 1'; if (@with_standby = 'Y') begin set @Message += @standby_command; end; else begin set @Message += ', NORECOVERY'; end; if (@stats > 0) begin set @Message += ', STATS = ' + cast(@stats as varchar(3)); end; set @RestoreDatabaseSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; if @execute = 'Y' begin if (@is_clear_backup_history = 'Y') begin raiserror(@break,0,1) with nowait; raiserror(@break,0,1) with nowait; exec #ClearHistory; raiserror(@break,0,1) with nowait; end; raiserror('/*',0,1); exec (@RestoreDatabaseSqlCmd); raiserror('*/',0,1) with nowait; end; raiserror(@break,0,1) with nowait; end; else if (@use_differentials_for_restore_path = 'Y') begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * No differential backups exist for this database and/or restore time.',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@break,0,1) with nowait; end; /* Generate LOG file statements */ declare @RestoreLogSqlCmd varchar(4000) = '' ,@CurrentFileName varchar(4000) = '' ,@FullDiffLastLSN numeric(25,0); if exists (select * from #LogBackupFiles) begin --compare the dates to see if the log should be restored. set @FullDiffLastLSN = (select z.LastLsn from #BackupHeader z); -- get rid of logs that do not apply to this restore path. If any exist that are from a datetime greater than @restore_datetime, -- they cannot possibly be needed. delete #LogBackupFiles where FileDateTimeDt > ( select min(a.FileDateTimeDt) from #LogBackupFiles a where a.FileDateTimeDt > @restore_datetime ); end; declare @StartLogTime datetime ,@LogRestoreDuration varchar(50) ,@LogTimeSeconds int ,@LogFirstLSN numeric(25,0) ,@LogLastLSN numeric(25,0) ,@CurrentFileId int; declare @IsFirstLogFileFound bit = 0 ,@LSNDetails varchar(4000) ,@CurrentLogCommand varchar(4000) ,@iteration_return_code int ,@iteration_return_code_char varchar(4000) ,@is_no_exec_printed bit = 0 ,@errorMsg varchar(4000) ,@errorSwiggles varchar(4000) = replace(@stars,'*','!') ,@RestoreAsTime datetime; set @iteration_return_code = 0; if exists (select * from #LogBackupFiles) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * -- LOG Restores Start -- *'; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * Searching For compatible log files using the header info:',0,1) with nowait; raiserror(@stars,0,1) with nowait; -- For printed commmands, set NOEXEC ON. That way if they are executed the "header" only statements won't -- actually be executed. raiserror('SET NOEXEC ON',0,1) with nowait; while exists (select * from #LogBackupFiles where IsDiscarded = 0) begin set @CurrentLogCommand = null; /** Restore files in order by sequence **/ select top (1) @CurrentFileName = a.filename_and_directory ,@CurrentFileId = a.Id from #LogBackupFiles a where a.IsDiscarded = 0 and a.IsProcessed = 0 order by a.Id asc; if @CurrentFileName is null begin raiserror('End of Log File Restore:',0,1) with nowait; break; end; if @IsFirstLogFileFound = 0 begin /* Get the log record that is first after the full/diff. (log.first_lsn <= fd.last_lsn && log.last_lsn > fd.last_lsn) = first restorable log file. In a log backup, first_lsn is the LSN of the first log record in the backup, and starting with this log record, the log backup includes log records up to but excluding the log record whose LSN is last_lsn. Two log backups are consecutive if and only if the LSN of the last log record in the earlier backup (Backup_A) is greater than or equal to the LSN of the first log record in the later backup (Backup_B); that is, Backup_A.last_lsn >= Backup_B.first_lsn. If this is not true, a gap exists between the two backups. If there is a gap between two log records, they cannot be restored. */ truncate table #BackupHeader_native; truncate table #BackupHeader_Litespeed; truncate table #BackupHeader; if (@backup_software = 'NATIVE') begin set @CurrentLogCommand = 'restore headeronly from disk = ''' + @CurrentFileName + ''''; raiserror(@stars, 0, 1) with nowait; raiserror(@CurrentLogCommand,0,1) with nowait; raiserror(@break,0,1) with nowait; insert #BackupHeader_native exec (@CurrentLogCommand); end; else if (@backup_software = 'LITESPEED') begin set @CurrentLogCommand = ' exec master.dbo.xp_restore_headeronly @filename = ''' + @CurrentFileName + ''''; raiserror(@stars, 0, 1) with nowait; raiserror(@CurrentLogCommand,0,1) with nowait; raiserror(@break,0,1) with nowait; -- Litespeed can be used with native SQL Backups. If a native format is used, use the native restore #temp table. -- assume an error on the INSERT, because a native SQL Backup is used. begin try insert #BackupHeader_Litespeed exec (@CurrentLogCommand); end try begin catch insert #BackupHeader_native exec (@CurrentLogCommand); end catch; end; insert #BackupHeader ( FirstLSN ,LastLSN ) select b.FirstLSN ,b.LastLSN from #BackupHeader_Litespeed b union all select b.FirstLSN ,b.LastLSN from #BackupHeader_native b; select @LogFirstLSN = FirstLSN ,@LogLastLSN = LastLSN from #BackupHeader; update #LogBackupFiles set StartLSN = @LogFirstLSN , LastLSN = @LogLastLSN where Id = @CurrentFileId; --Get the log details /* Find the log.first_lsn <= fd.last_lsn && log.last_lsn > fd.last_lsn. After this log is found, continue with the restore. */ if ( (@LogFirstLSN <= @FullDiffLastLSN and @LogLastLSN > @FullDiffLastLSN) or (@use_estimates_for_headeronly_metadata = 'Y') ) begin set @IsFirstLogFileFound = 1; raiserror('/*',0,1) with nowait; if (@use_estimates_for_headeronly_metadata = 'Y') begin raiserror('estimating what log file to use. If you get strange errors, set @use_estimates_for_headeronly_metadata = ''N''', 0, 1) with nowait end else begin set @LSNDetails = space(5) + 'First Log LSN: ' + cast(@LogFirstLSN as varchar(100)); raiserror(@LSNDetails,0,1) with nowait; set @LSNDetails = space(5) + 'Full/Diff Last LSN: ' + cast(@FullDiffLastLSN as varchar(100)); raiserror(@LSNDetails,0,1) with nowait; set @LSNDetails = space(5) + 'Last Log LSN: ' + cast(@LogLastLSN as varchar(100)); raiserror(@LSNDetails,0,1) with nowait; set @LSNDetails = space(5) + 'First compatible log file: ' + @CurrentFileName; raiserror(@LSNDetails,0,1) with nowait; end raiserror('*/',0,1) with nowait; raiserror(@break,0,1) with nowait; end; else begin --This file isn't compatible with the restore. update #LogBackupFiles set IsDiscarded = 1 where Id = @CurrentFileId; /* -- debug select * from #LogBackupFiles lbf */ -- we didn't find any logs that could be used, report back. -- set the @RestoreAsTime as the last log file checked. if not exists (select * from #LogBackupFiles lbf where lbf.IsDiscarded = 0) begin raiserror(' -- No log files to restore.',0,1) with nowait; raiserror(@break,0,1) with nowait; end; --restart the loop find the first log backup compatible with the full/diff restore. continue; end; end; if (@is_no_exec_printed = 0) begin -- for the printed header commands, set this back off. raiserror('SET NOEXEC OFF',0,1) with nowait; raiserror(@break,0,1) with nowait; set @is_no_exec_printed = 1; end; -- for the commands printed to the screen, make the variables unique. This applies to LiteSpeed only -- since Litespeed uses stored procedures for the restores. set @iteration_return_code += 1; set @iteration_return_code_char = @iteration_return_code; if ( @backup_software = 'NATIVE') begin set @Message = ''; set @RestoreLogSqlCmd = ''; set @Message = 'restore log ' + quotename(@target_database_name) + ' from'; set @RestoreLogSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = ' disk = ''' + @CurrentFileName + ''''; set @RestoreLogSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = ' with FILE = 1'; set @RestoreLogSqlCmd += @Message; raiserror(@Message,0,1) with nowait; set @Message = ', STOPAT = ' + '''' + @restore_datetime_varchar + ''''; set @RestoreLogSqlCmd += @Message; raiserror(@Message,0,1) with nowait; if (@with_standby = 'Y') begin set @Message = @standby_command; set @RestoreLogSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; set @Message = ''; set @RestoreLogSqlCmd += @Message; raiserror(@Message,0,1) with nowait; end; else if (@backup_software = 'LITESPEED') begin -- build restore statement using litespeed extended stored procedure. set @Message = 'declare @rMsg' + @iteration_return_code_char + ' varchar(999), @rCode' + @iteration_return_code_char + ' int; '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd = @Message; set @Message = 'exec master.dbo.xp_restore_log '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = ' @database = ' + quotename(@target_database_name,''''); set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = ' ,@filename = ' + '''' + @CurrentFileName + ''''; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = ' ,@filenumber = 1'; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = ' ,@with = ''STOPAT = ''''' + @restore_datetime_varchar + ''''''''; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; if (@with_standby = 'Y') begin set @Message = @standby_command; set @RestoreLogSqlCmd += @Message; raiserror(@Message,0,1) with nowait; --todo select @standby_command end; set @Message = ' ,@resultMsg = @rmsg' + @iteration_return_code_char + ' output '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = ' ,@resultcode = @rCode' + @iteration_return_code_char + ' output '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = ''; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = 'if (@rCode' + @iteration_return_code_char + ' <> 0)'; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = 'begin '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = space(5) + 'raiserror(''-- *********************************************************************'',0,1) with nowait '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = space(5) + 'raiserror(''-- * ReturnCode: %%i'',0,1, @rCode' + @iteration_return_code_char + ' ) with nowait '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = space(5) + 'raiserror(''-- * ReturnMessage: %%s'',0,1, @rMsg' + @iteration_return_code_char + ' ) with nowait '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = space(5) + 'raiserror(''-- **********************************************************************'',0,1) with nowait '; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = 'end'; set @Message += space(1); raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; set @Message = ''; raiserror(@Message,0,1) with nowait; set @RestoreLogSqlCmd += @Message; end; if (@is_clear_backup_history = 'Y') begin exec #print_ClearHistory; end; set @StartLogTime = current_timestamp; if ( @execute = 'Y' ) begin if (@is_clear_backup_history = 'Y') begin raiserror(@break,0,1) with nowait; raiserror(@break,0,1) with nowait; exec #ClearHistory; raiserror(@break,0,1) with nowait; end; -- separator for output messages. raiserror('/*',0,1); raiserror(@stars,0,1); -- if there was an error restoring any of the log files, stop right away. begin try exec (@RestoreLogSqlCmd); end try begin catch set @errorMsg = error_message(); -- If the 'estimates' for the header were used. -- The log backups that match the LSNs of the full/diff can't be determined, so -- keep on looping through the backups until we find the right one if (@use_estimates_for_headeronly_metadata = 'Y') begin raiserror('-- * Error restoring log, continuing on (this is normal with @use_estimates_for_headeronly_metadata = ''Y'' ): %s',0,1, @CurrentFileName) with nowait; raiserror(@stars,0,1) with nowait; end else begin raiserror(@errorSwiggles,0,1) with nowait; raiserror('Error restoring log: %s',0,1, @CurrentFileName) with nowait; raiserror(@errorMsg,0,1) with nowait; raiserror(@errorSwiggles,0,1) with nowait; delete #LogBackupFiles from #LogBackupFiles b where b.Id >= @CurrentFileId; end end catch; raiserror('*/',0,1) with nowait; end; set @LogTimeSeconds = datediff(second,@StartLogTime,current_timestamp); set @LogRestoreDuration = '-- * Total Log file restore time: ' + ltrim(str(@LogTimeSeconds)) + ' seconds'; -- Informational to see how long each log file is taking. -- when @execute = 'N', this will still be displayed since it helps with reading the output. -- also serves as whitespace to help readability. raiserror(@stars,0,1) with nowait; raiserror(@LogRestoreDuration,0,1) with nowait; raiserror(@stars,0,1) with nowait; update #LogBackupFiles set IsProcessed = 1, IsDiscarded = 1 where filename_and_directory = @CurrentFileName; if (@execute = 'Y') if exists (select * from sys.databases where name = @target_database_name and state = 0) -- Done if recovered. -- State of 0 means the database has recovered. begin -- for the printed commands, stop executing the log restores because the database is recovered. -- SET NOEXEC ON prevents any sql from being run. -- we'll set it back on when we're ready to continue. break; end; end; -- for the printed commands, SET NOEXEC OFF. we want the rest of the restore commands to run. raiserror('SET NOEXEC OFF',0,1) with nowait; -- we expected to find some log files to restore, but didn't. -- this could happen if there weren't any changes from the full backup to the log backups. -- If the Last LSNs never change throughout all of the log files, then we know this is the case. declare @LogSequenceNumbersCount_LastLSN int = 0; if ( @IsFirstLogFileFound = 0 and not exists (select * from #LogBackupFiles lbf where lbf.LastLSN is null) and exists (select * from #LogBackupFiles lbf) and @check_restore_time_integrity = 'Y' ) begin raiserror('/*',0,1) with nowait; raiserror(' There are no changes to roll forward with the log backups.',0,1) with nowait; raiserror('*/',0,1) with nowait; set @RestoreAsTime = (select top (1) lbf.FileDateTimeDt from #LogBackupFiles lbf order by lbf.Id desc); -- No log files to roll foward, so backdate the restore as time so it makes sense. -- this shouldn't happen often. if ( @RestoreAsTime > @restore_datetime ) begin set @RestoreAsTime = @restore_datetime; end; end; raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * -- LOG Restores End -- *'; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@break,0,1) with nowait; end; else if (@use_logs_for_restore_path = 'Y') begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * No Log backups exist for this database and/or restore time.',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@break,0,1) with nowait; end; raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * -- Put database in Recovery Mode/Online -- *'; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@break,0,1) with nowait; -- Figuring out what time the database actually restored as. declare @RestoreAsTime_varchar varchar(50); declare @is_recovered_to_point_in_time bit = 0; /* select * from #FullBackupFiles fbf select * from #DifferentialBackupFiles dbf select * from #LogBackupFiles lbf */ /* 1. STOPAT time met 2. NULL restore_datetime, not met, but within wiggle room. 3. FULL or DIFF only restore 4. no LOGS were restored. Set to @restore_datetime or the last log file datetime. */ -- if the log files were used and the database was recovered, then the restore time is exact. -- this will be used in the informational messages to tell the caller if the restore time -- is exact or approximate. -- If no log files were used ( i.e. database in SIMPLE recovery or @use_logs_for_restore_path = 'N' ) -- declare @is_recovered_to_point_in_time bit = 0; if @RestoreAsTime is null begin set @RestoreAsTime = (select top (1) LBF.FileDateTimeDt from #LogBackupFiles LBF where LBF.IsProcessed = 1 order by LBF.FileDateTimeDt desc); end; -- the database is online, so the STOPAT time was met. if ( @RestoreAsTime is not null and @orig_restore_datetime is not null and exists (select * from sys.databases where name = @target_database_name and state = 0) --online ) begin set @RestoreAsTime = @restore_datetime; set @is_recovered_to_point_in_time = 1; end; if @RestoreAsTime is null begin set @RestoreAsTime = (select distinct DBF.FileDateTimeDt from #DifferentialBackupFiles DBF); if @RestoreAsTime is null begin set @RestoreAsTime = (select distinct FBF.FileDateTimeDt from #FullBackupFiles FBF); end; end; set @RestoreAsTime_varchar = convert(varchar(50),@RestoreAsTime,121); declare @secondsOffRestore int; declare @is_pit_integrity_error bit = 0; if @execute = 'Y' begin -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ Restore the database in a recovered state no matter what, but raise an error -- ~ with the details if the restore time isn't exact. The error is flagged -- ~ just below this code block (these two code blocks are directly related. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ declare @IsDatabaseRestoreTimeWhatIsExpected bit = 0; declare @IsErrored bit = 0; declare @ErrorNumber int = 0; /* debug select secondsOffRestore = @secondsOffRestore ,RestoreTimeOffSet_seconds = @RestoreTimeOffSet_seconds ,IsExactRestoreTimeRequired = @is_exact_restore_time_required ,check_restore_time_integrity = @check_restore_time_integrity ,use_logs_for_restore_path = @use_logs_for_restore_path */ --1. no exact time was specified for the restore (@restoreDatetime = NULL). --2. An exact time is required. If the database is not in a recovered state, it will be caught below. --3. The @check_restore_time_integrity option is set to 'N'. declare @restore_msg varchar(4000); declare @difference_restore_time decimal(8,2); set @secondsOffRestore = datediff(second,@RestoreAsTime, @restore_datetime); set @difference_restore_time = datediff(second,@RestoreAsTime, @restore_datetime) / 60.0; ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -- The restore time offset tolerance allowed if @restore_datetime is not specified (NULL). -- e.g. if @restore_datetime IS NULL, @restore_offset_allowance_minutes = 10 [minutes], Restore start time = 6:58, and the database is restored as of 6:50. -- this is allowed, it is within the window (as long as an exact restore time is not specified). -- However, if @restore_datetime IS NULL, the restore start time = 6:58, and the database is restored as of 6:45. -- this would be > 10 minutes) . So it is not allowed. after the database is restored, an error will be displayed -- informing the caller of what happened. -- if an exact time is specified, then this does not apply. It only applies if @restore_datetime is not specified (NULL). -- This also does not apply if @check_restore_time_integrity = 'N'. ---------------------------------------------------------------------------------------------------------------------------------------------------------------- declare @RestoreTimeOffsetToleranceInSeconds int; set @RestoreTimeOffsetToleranceInSeconds = ( @restore_offset_allowance_minutes * 60 ); if ((@secondsOffRestore <= @RestoreTimeOffsetToleranceInSeconds) or (@check_restore_time_integrity = 'N')) begin set @IsDatabaseRestoreTimeWhatIsExpected = 1; set @restore_msg = '-- * Restore datetime: ' + space(7) + @RestoreAsTime_varchar; raiserror(@restore_msg,0,1) with nowait; set @restore_msg = '-- * Target restore datetime: ' + @restore_datetime_varchar; raiserror(@restore_msg,0,1) with nowait; if ( @check_restore_time_integrity = 'Y') begin set @restore_msg = '-- * Offset allowance: ' + ltrim(str(@restore_offset_allowance_minutes)) + ' minutes.'; raiserror(@restore_msg,0,1) with nowait; if (@restore_offset_allowance_minutes <> 0) begin set @restore_msg = '-- * Difference (Target - Actual) : ' + cast(@difference_restore_time as varchar(50)) + ' minutes'; raiserror(@restore_msg,0,1) with nowait; end; end; else begin raiserror('-- * Restore time integrity not checked. @check_restore_time_integrity = ''N''', 0, 1) with nowait; end; raiserror(@stars,0,1) with nowait; raiserror('',0,1) with nowait; end; else begin select @ErrorNumber = 1 ,@IsErrored = 1 ,@is_pit_integrity_error = 1; raiserror(@stars,0,1) with nowait; set @restore_msg = '-- * WARNING! This database is NOT restored as of the datetime specified.'; raiserror(@restore_msg,0,1) with nowait; set @restore_msg = '-- * Actual restore datetime: ' + @RestoreAsTime_varchar; raiserror(@restore_msg,0,1) with nowait; set @restore_msg = '-- * Target restore datetime: ' + @restore_datetime_varchar; raiserror(@restore_msg,0,1) with nowait; set @restore_msg = '-- * Offset allowance: ' + ltrim(str(@restore_offset_allowance_minutes)) + ' minutes.'; raiserror(@restore_msg,0,1) with nowait; set @restore_msg = '-- * Difference (Target - Actual) : ' + cast(@difference_restore_time as varchar(50)) + ' minutes'; raiserror(@restore_msg,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @restore_msg = '-- * Below is SQL to put the database in a recovered state, if needed.'; raiserror(@restore_msg,0,1) with nowait; raiserror(@stars,0,1) with nowait; end; end; -- Build recovery statement, this will only be executed if -- the database hasn't been restored already. -- set database in working state declare @SetDatabaseInWorkingState varchar(4000) = 'restore database ' + quotename(@target_database_name) + ' with recovery'; declare @recoveryText varchar(4000); set @recoveryText = 'if exists ( select * from sys.databases d where d.name = ' + quotename(@target_database_name,'''') + ' and [state] = 0 and d.is_in_standby = 1 ) or exists ( select * from sys.databases d where d.name = ' + quotename(@target_database_name,'''') + ' and d.[state] <> 0 )'; raiserror(@recoveryText,0,1) with nowait; raiserror(@SetDatabaseInWorkingState,0,1) with nowait; raiserror(@break,0,1) with nowait; -- if the database is not in recovery, set it if needed if ( @execute = 'Y' and @is_pit_integrity_error = 0) begin if exists ( select * from sys.databases d where d.name = @target_database_name and state = 0 and d.is_in_standby = 1 ) or exists ( select * from sys.databases d where d.name = @target_database_name and d.state <> 0 ) begin raiserror('/*',0,1) with nowait; exec (@SetDatabaseInWorkingState); raiserror('*/',0,1) with nowait; end; end; -- Set recovery method declare @recovery_methodSql varchar(4000); if (@recovery_method is not null) begin set @recovery_methodSql = 'alter database ' + quotename(@target_database_name) + ' set recovery'; set @recovery_methodSql += ' ' + @recovery_method; raiserror(@recovery_methodSql,0,1) with nowait; if ( @execute = 'Y' and @is_pit_integrity_error = 0) exec (@recovery_methodSql); end; declare @onlineSql varchar(4000); -- This database has previously existed, so it is in an offline state. -- this sets the database online. if @does_database_exist = 1 begin set @onlineSql = 'alter database ' + quotename(@target_database_name) + ' set online'; raiserror(@onlineSql,0,1) with nowait; if ( @execute = 'Y' and @is_pit_integrity_error = 0 ) exec (@onlineSql); end; raiserror(@break,0,1) with nowait; --double check the databases is in a working state. if @execute = 'Y' begin if not exists (select * from sys.databases where name = @target_database_name and state = 0) begin raiserror('Restore failed. See above for explanation.', 16,1,@target_database_name) with nowait; return (1); end; end; -- Shrink log file to it's minimum. Typically this is used for reporting/test purposes, since -- these servers are read-only and/or have limited drive space. if ( @is_shrink_log_after_restore = 'Y' and @execute = 'Y' ) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * Shrink Log File Start. '; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; declare @LogId int; declare @TbLogId table (LogId int); declare @error int; declare @rowcount int; declare @sqlLogId varchar(4000) = ''; set @sqlLogId = ' SELECT [file_id] FROM ' + quotename(@target_database_name ) + '.sys.database_files where type_desc = ''LOG'''; insert @TbLogId (LogId) exec (@sqlLogId); select @error = @@error ,@rowcount = @@rowcount; if ( @error <> 0 or @rowcount <> 1 ) begin raiserror('Error generating shrinking statement for the log file.',16,1) with nowait; return (1); end; else set @LogId = (select * from @TbLogId); -- the file name or database name is wrong. Or, this script needs to be updated to support more than -- 1 transaction log file (is having more than 1 transaction log file really a thing?) if @LogId is null begin raiserror('Error setting the @LogId for the shrinking of the log file.',16,1) with nowait; return (1); end; raiserror('GO',0,1) with nowait; -- Shrink the log file set @sqlLogId = space(3) + 'use ' + quotename(@target_database_name) + '; DBCC SHRINKFILE (' + cast(@LogId as char(1)) + ', 100) with no_infomsgs;'; raiserror(@sqlLogId,0,1) with nowait; raiserror(@break,0,1) with nowait; exec (@sqlLogId); if ( @@error <> 0 ) begin raiserror('Error shrinking the log file.',16,1) with nowait; return (1); end; end; --fix orphaned users, if the SIDs between the database users and logins don't match. declare @fixUsersSql varchar(4000) = ' exec master.dbo.FixOrphanedUsers ' + quotename(@target_database_name,''''); if (@is_master = 0 and @is_model = 0) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * -- Fix orphaned users-- *'; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@fixUsersSql,0,1) with nowait; raiserror(@break,0,1) with nowait; if @execute = 'Y' begin exec (@fixUsersSql); end; end; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ Fix db diagrams - https://stackoverflow.com/questions/2043382/database-diagram-support-objects-cannot-be-installed-no-valid-owner/ -- ~ This is done by fixing the database owner. It should always be sa, unless it shouldn't. :) -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ declare @fix_diagram_sql varchar(4000); set @fix_diagram_sql = ' alter authorization on database::' + quotename(@target_database_name) + ' to sa;'; if (@change_db_owner_to_sa = 'Y') begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- ~ Change database owner.',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@fix_diagram_sql,0,1) with nowait; raiserror(@break,0,1) with nowait; raiserror(@break,0,1) with nowait; if (@execute = 'Y') begin exec (@fix_diagram_sql); end; end; declare @broker_sql varchar(4000); if (@enable_broker is not null) begin set @broker_sql = 'alter database ' + quotename(@target_database_name) + ' set ' + @enable_broker + ' with rollback immediate;'; raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- ~ Enable service broker',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@broker_sql,0,1) with nowait; raiserror(@break,0,1) with nowait; raiserror(@break,0,1) with nowait; if (@execute = 'Y') begin exec (@broker_sql); end; end; if (@is_create_snapshot = 'Y' and @execute = 'Y') begin declare @snapshot_sql varchar(max) = '' declare @snapshot_logical_name varchar(max) = '' declare @snapshot_filename varchar(max) = '' declare @targetdb varchar(255) set @targetdb = quotename(@target_database_name + '_SS') declare oncmd cursor for select OnCmd = '(NAME=''' + name + ''', FILENAME=''' + @new_server_data_drive_and_directory + isnull(@optional_restore_name + '_','') + name + '.ss1'')' from sys.master_files where type = 0 and database_id = db_id(@target_database_name) declare @oncmd varchar(500) set @snapshot_sql = '' open oncmd fetch next from oncmd into @oncmd while @@fetch_status = 0 begin if @snapshot_sql <> '' set @snapshot_sql = @snapshot_sql + ', ' + char(10) set @snapshot_sql = @snapshot_sql + @oncmd fetch next from oncmd into @oncmd end close oncmd deallocate oncmd set @snapshot_sql = 'CREATE DATABASE ' + @targetdb + ' ON ' + char(10) + @snapshot_sql set @snapshot_sql = @snapshot_sql + char(10) + 'AS SNAPSHOT OF ' + @target_database_name --set @snapshot_sql = 'create database ' + quotename(@target_database_name + '_SS') --+ ' ON (NAME = ''' + @snapshot_logical_name + '''' + ',' + 'FILENAME = ''' + @new_server_data_drive_and_directory --+ @target_database_name + '.ss1'') AS SNAPSHOT OF ' + @target_database_name raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- ~ Create Snapshot',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@snapshot_sql,0,1) with nowait; raiserror(@break,0,1) with nowait; raiserror(@break,0,1) with nowait; if (@execute = 'Y') begin exec (@snapshot_sql); end; end else if (@is_create_snapshot = 'Y' and @execute = 'N') begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- ~ Create Snapshot',0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror(' -- creating a snapshot is only supported when @Execute = ''Y''',0, 1) with nowait raiserror(@break,0,1) with nowait; raiserror(@break,0,1) with nowait; end -- if there was an error along the way, call it out. The DB will still be in an operational state (if possible). -- let the caller decide if it's okay or not. if (@IsErrored = 1) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * There was an error with restoring: %s. Check the above output and see if this is okay or not.',16,1, @target_database_name) with nowait; raiserror(@stars,0,1) with nowait; return (1); end; raiserror(@dashes, 0, 1) with nowait; set @Message = '-- * -- Complete: ' + case when @execute = 'N' then 'Not Executed' else 'Executed' end + ' -- *'; raiserror(@Message,0,1) with nowait; set @Message = @dashes; raiserror(@Message,0,1) with nowait; set @Message = '-- * Database: ' + @target_database_name + ' -- *'; raiserror(@Message,0,1) with nowait; --total runtimes for the entire process. declare @Minute int, @Second int, @TotalSeconds int; select @TotalSeconds = datediff(second,@StartTime,current_timestamp) ,@Minute = @TotalSeconds / 60 ,@Second = @TotalSeconds % 60; set @Message = '-- * Minutes: ' + cast(@Minute as varchar(100)) + ' -- *'; raiserror(@Message,0,1) with nowait; set @Message = '-- * Seconds: ' + cast(@Second as varchar(100)) + ' -- *'; raiserror(@Message,0,1) with nowait; raiserror(@dashes,0,1) with nowait; raiserror(@break,0,1) with nowait; -- error this proc if something wasn't quite right. if ( @IsErrored = 1) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('Error: restoring database %s. Examine and decide if the restored database is usable.',16,1,@target_database_name) with nowait; raiserror(@stars,0,1) with nowait; raiserror(@break,0,1) with nowait; end; declare @checkdb_cmd nvarchar(4000); declare @rc_checkdb int = 1; if ( @check_db = 'Y') begin set @checkdb_cmd = 'exec @rc_from_dynamic_call = master.dbo.DatabaseIntegrityCheck @Databases = ' + quotename(@target_database_name) + ' ,@execute = ''' + @execute + ''''; raiserror(@stars,0,1) with nowait; raiserror('-- ~ Check database integrity - (DBCC checkdb) -- *',0,1) with nowait; raiserror(@stars,0,1) with nowait; -- define var for the commands printed to screen raiserror('declare @rc_from_dynamic_call int; ',0,1) with nowait; raiserror(@checkdb_cmd,0,1) with nowait; raiserror(@break,0,1) with nowait; if (@execute = 'Y' ) begin raiserror('/*', 0, 1) with nowait; exec sys.sp_executesql @checkdb_cmd, N'@rc_from_dynamic_call int output', @rc_from_dynamic_call = @rc_checkdb output; if (@rc_checkdb <> 0) begin raiserror('Error running checkdb. Check above errors to see what went wrong.', 16, 1) with nowait; return (1); end; raiserror('*/', 0, 1) with nowait; --total time for checkdb + restore raiserror(@break, 0 ,1 ) with nowait; raiserror(@dashes,0,1) with nowait; set @Message = '-- * Total Time -- *'; raiserror(@Message,0,1) with nowait; set @Message = @dashes; raiserror(@Message,0,1) with nowait; set @Message = '-- * Database: ' + @target_database_name + ' -- *'; raiserror(@Message,0,1) with nowait; select @TotalSeconds = datediff(second,@StartTime,current_timestamp) ,@Minute = @TotalSeconds / 60 ,@Second = @TotalSeconds % 60; set @Message = '-- * Minutes: ' + cast(@Minute as varchar(100)) + ' -- *'; raiserror(@Message,0,1) with nowait; set @Message = '-- * Seconds: ' + cast(@Second as varchar(100)) + ' -- *'; raiserror(@Message,0,1) with nowait; raiserror(@dashes,0,1) with nowait; raiserror(@break,0,1) with nowait; end; end; declare @drop_sql varchar(4000); -- after all the hard work getting to this point, drop the database after a successful restore if specified. if (@drop_after_restore = 'Y') begin -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- ~ drop all database snapshots if they exist (copy pasta from beginning of this SP) -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if object_id('tempdb..#snapshots2') is not null drop table #snapshots2; select snapshot_name = d.name ,snapshot_database_id = d.database_id ,is_done = 0 into #snapshots2 from sys.databases d where source_database_id = db_id(@target_database_name); if (@@rowcount > 0) begin raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; raiserror('-- * Dropping Database Snapshots',0,1) with nowait; raiserror(@stars,0,1) with nowait; end; while exists (select * from #snapshots2 z where z.is_done = 0) begin set @sql_snapshot_drop = ''; select top (1) @snapshot_to_drop = snapshot_name from #snapshots2 z where is_done = 0; set @sql_snapshot_drop = ' drop database ' + quotename(@snapshot_to_drop) + ';'; raiserror(@sql_snapshot_drop,0,1) with nowait; if (@execute = 'Y') begin exec (@sql_snapshot_drop); end; update #snapshots2 set is_done = 1 where snapshot_name = @snapshot_to_drop; end; raiserror(@break,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @Message = '-- * -- Drop Database -- *'; raiserror(@Message,0,1) with nowait; raiserror(@stars,0,1) with nowait; set @drop_sql = 'use [master]; alter database ' + quotename(@target_database_name) + ' set single_user with rollback immediate; '; set @drop_sql += ' drop database ' + quotename(@target_database_name) + ';'; raiserror(@drop_sql,0,1) with nowait; raiserror(@break,0,1) with nowait; if (@execute = 'Y') begin exec (@drop_sql); end; end; end; GO -- Create supporting procedures use master; go SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO if object_id('dbo.FixOrphanedUsers') is null exec ('create procedure dbo.FixOrphanedUsers as return 0'); GO alter procedure dbo.FixOrphanedUsers @Database varchar(128) as begin set nocount on; set transaction isolation level read uncommitted; declare @OrphanedUsers int, @Count int = 1, @LoginName varchar(128), @UserName varchar(128), @IsReadOnly bit = 0, @sql nvarchar(4000); if not exists (select * from sys.databases where name = @Database and [state] = 0 and is_in_standby = 0) begin print 'Database does not exist or online.'; return (1); end; set @Database = quotename(@Database); declare @FixUsers table ( id int identity(1,1) ,LoginName varchar(128) ,UserName varchar(128) ); set @sql = 'use ' + @Database + '; select LoginName = s.name ,UserName = d.name from sys.server_principals s join sys.database_principals d on lower(s.name) = lower(d.name) collate SQL_Latin1_General_CP1_CI_AS where s.[type] = ''S'' and s.[sid] is not null and s.[sid] <> 0x0 and suser_sname(d.[sid]) is null'; insert @FixUsers (LoginName, UserName) exec (@sql); set @OrphanedUsers = @@rowcount; if @OrphanedUsers = 0 begin raiserror(N'--No orphaned users to fix.',0,1)with nowait; return; end; -- Database is read only, switch it to read_write. -- It will be switched back to read only at the end. if exists (select * from sys.databases where is_read_only = 1 and name = @Database collate SQL_Latin1_General_CP1_CI_AS) begin set @IsReadOnly = 1; print N'Setting database to read_write'; set @sql = ' alter database ' + @Database + ' set single_user with rollback immediate;' + char(10) + ' alter database ' + @Database + ' set read_write with no_wait '; exec sys.sp_executesql @sql; if not exists (select * from sys.databases where name = @Database and is_read_only = 0) begin raiserror('unable to set database to read_write',16,1); return (1); end; end; declare @IsReadOnlyError bit = 0; raiserror('--Fix Statements:',0,1) with nowait; declare @stars varchar(200) = '-- ' + replicate('*',100); raiserror(@stars,0,1) with nowait; --associate all orphaned users with its login. while @Count <= @OrphanedUsers begin select @LoginName = quotename(f.LoginName) ,@UserName = quotename(f.UserName) from @FixUsers f where @Count = id; if ( @LoginName is null or @UserName is null ) break; else begin set @sql = 'use ' + @Database + N' alter user ' + @UserName + N' with login = ' + @LoginName; raiserror(@sql,0,0) with nowait; exec sys.sp_executesql @sql; set @Count += 1; end; end; raiserror(@stars,0,1) with nowait; --Setting the database to read only if it was not previously read write. if @IsReadOnly = 1 begin raiserror('Setting to read only',0,1) with nowait; set @sql = 'alter database ' + @Database + ' set multi_user; ' + ' alter database ' + @Database + ' set read_only with no_wait'; exec sys.sp_executesql @sql; if not exists (select * from sys.databases where name = @Database and is_read_only = 1) begin raiserror('error setting database to read only',16,1); set @IsReadOnlyError = 1; end; end; if @IsReadOnlyError = 1 raiserror('error setting database to read only',16,1) with nowait; if @IsReadOnly = 1 return 1; end;