Skip to content

Proc FB_DBRestore

Jump to bottom
EdVassie edited this page Jul 19, 2019 · 5 revisions
Previous Proc FB_AGPostFailover Large Scale Deployment Using SQL FineBuild Next

The FB_DBRestore procedure performs a database restore that includes the following features:

The rest of this page contains the following sections

Syntax

  master.dbo.FB_DBRestore [ @DatabaseName = ] 'target_database_name'
   [ , [ @Server = ]      'server_hosting_backup' ]
   [ , [ @DataPath = ]    'path_for_data_files' ]
   [ , [ @LogPath = ]     'path_for_log_files' ]
   [ , [ @SourceDB = ]    'source_database_name' ]
   [ , [ @StopAt = ]      'marker_for_restore_end' ]
   [ , [ @CopyOnly = ]    'allow_copyonly_backups' ]
   [ , [ @FullApply = ]   'allow_full_backups' ]
   [ , [ @DiffApply = ]   'allow_differential_backups' ]
   [ , [ @LogApply = ]    'allow_log_backups' ]
   [ , [ @DBFolder = ]    'create_database_subfolder' ]
   [ , [ @DBOwner = ]     'owner_account' ]
   [ , [ @Recovery = ]    'end_in_recovery_mode' ]
   [ , [ @Standby = ]     'end_in_standby_mode' ]
   [ , [ @Checksum = ]    'validate_checksum' ]
   [ , [ @Execute = ]     'execute_restore_process' ]
   [ , [ @SetupReseed = ] 'reseed_secondary_databases' ]
   [ , [ @RemoteCall = ]  'internal_use_only' ]
   [ , [ @DBStatus = ]    'internal_use_only' ]
   [ , [ @DBLSN = ]       'internal_use_only' ]
   [ , [ @SQLRestore = ]  'restore_command' OUTPUT ]

Arguments

[ @DatabaseName = ] 'target_database_name' Name of target Database after Restore complete. target_database_name is nvarchar(200), with no default.

[ @Server = ] 'server_hosting_backup' Name of Server hosting Backup details. server_hosting_backup is nvarchar(200), with default as the current SQL instance.

[ , [ @DataPath = ] 'path_for_data_files' ] Path to target data files. path_for_data_files is nvarchar(200), with default as the current default data file path.

[ , [ @LogPath = ] 'path_for_log_files' ] Path to target Log files. path_for_log_files is nvarchar(200), with default as the current default log file path.

[ , [ @SourceDB = ] 'source_database_name' ] Name of source database for the backup. source_database_name is nvarchar(200), with default as target_database_name.

[ , [ @StopAt = ] 'marker_for_restore_end' ] Time Stamp or Log Marker for end of restore process. marker_for_restore_end is nvarchar(24) with no default, which means the recovery will continue to the end of the backup files.

[ , [ @CopyOnly = ] 'allow_copyonly_backups' ] Allow the use of Copy Only backups. allow_copyonly_backups is varchar(1) with default = 'N', which means do not allow the use of Copy Only backups. Any other value will allow the use of Copy Only backups.

[ , [ @FullApply = ] 'allow_full_backups' ] Allow the use of Full backups. allow_full_backups is varchar(1) with default = 'Y', which means allow the use of Full backups. Any other value will prevent the use of Full backups.

[ , [ @DiffApply = ] 'allow_differential_backups' ] Allow the use of Differential backups. allow_differential_backups is varchar(1) with default = 'Y', which means allow the use of Differential backups. Any other value will prevent the use of Differential backups.

[ , [ @LogApply = ] 'allow_log_backups' ] Allow the use of Log backups. allow_log_backups is varchar(1) with default = 'Y', which means allow the use of Log backups. Any other value will prevent the use of Log backups.

[ , [ @DBFolder = ] 'create_database_subfolder' ] Create a sub-folder to hold data files. create_database_subfolder is varchar(1) with no default, which means create a sub-folder if more than one data file exists for the database. A value of 'Y' will force the creation of a sub-folder, any other value will prevent the creation of a sub-folder.

[ , [ @DBOwner = ] 'owner_account' ] Use the specified account as the Database Owner for the restored database. owner_account is varchar(120) with no default, which means use the following table to assign the Database Owner:

Database Name Owner Account
master sa
model sa
msdb sa
tempsb sa
SSISDB sa
distribution database sa
DQS databases ##MS_dqs_db_owner_login##
other databases account referenced in StandardDBOwner Credential

[ , [ @Recovery = ] 'end_in_recovery_mode' ] End the restore leaving the database in Recovery mode. end_in_recovery_mode is varchar(1) with no default, which means do not end the restore in Recovery mode. A value of 'Y' will force the restore to end leaving the database in Recovery mode.

  • Ending the restore in Recovery mode can be useful as part of preparing a database to bejoined to an Availability Group.

[ , [ @Standby = ] 'end_in_standby_mode' ] End the restore leaving the database in Standby mode. end_in_standby_mode is varchar(1) with no default, which means do not end the restore in Standby mode. A value of 'Y' will force the restore to end leaving the database in Standby mode.

  • Ending the restore in Standby mode can be useful as part of implementing a Log Shipping solution.

[ , [ @Checksum = ] 'validate_checksum' ] Validate the Checksum data in the restore files. validate_checksum is varchar(1) with default = 'Y', which means do validate the Checksum data. Any other value will prevent the validation of Checksum data.

[ , [ @Execute = ] 'execute_restore_process' ] Execute the database restore process. execute_restore_process is varchar(1) with default = 'N', which means do not execute the restore process. A value of 'Y' will cause the restore process to be executed.

  • Regardless of if the restore process is executed, the restore command will be printed to the Messages file, and will be returned to the caller in the @SQLRestore variable.

[ , [ @SetupReseed = ] 'reseed_secondary_databases' ] Reseed Secondary databases if 'target_database_name' is part of an Availability Group. reseed_secondary_databases is varchar(1) with default = 'Y', which means do reseed Secondary databases. Any other value will prevent the reseed of Secondary databases.

  • A Reseed of Secondary databases is only valid for SQL 2016 and above.

[ , [ @RemoteCall = ] 'internal_use_only' ] Do not use this parameter. It is used by FB_DBRestore when a restore needs to get information from another server.

[ , [ @DBStatus = ] 'internal_use_only' ] Do not use this parameter. It is used by FB_DBRestore when a restore needs to get information from another server.

[ , [ @DBLSN = ] 'internal_use_only' ] Do not use this parameter. It is used by FB_DBRestore when a restore needs to get information from another server.

[ , [ @SQLRestore = ] 'restore_command' OUTPUT ] Contains the restore comand. restore_command is nvarchar(max) with a default of '' and is an OUTPUT parameter, which means it will contained data generated by FB_DBRestore.

Return Code Values

TBC

Result Sets

TBC

Remarks

TBC

Cross-Server Restore

TBC

Permissions

TBC

Examples

This section contains the following examples:

Parameters from different examples can be combined as needed for the required restore situation

Restore a database to the same name and server as the original

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@Execute=       'Y'
  • The 'target_database_name' will be restored on the current server, overwriting any existing 'target_database_name'
  • The latest Full Backup for 'target_database_name' will be used, along with any Differential Backups and Log Backups that have been created between when the Full Backup was taken and the time FB_DBRestore is executed
  • If the database is part of an AG, all Secondaries will be re-seeded
  • The database will be left available for use

Restore a database to a different name from the original

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@SourceDB =     'source_database_name'
   ,@Execute=       'Y'
  • The 'target_database_name' will be restored on the current server, overwriting any existing 'target_database_name'
  • The latest Full Backup for 'source_database_name' will be used, along with any Differential Backups and Log Backups that have been created between when the Full Backup was taken and the time FB_DBRestore is executed
  • If the database is part of an AG, all Secondaries will be re-seeded
  • The database will be left available for use

Restore a database to a different location from the original

TBC

Restore a database to a different server from the original

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@Server =       'server_hosting_backup'
   ,@Execute=       'Y'
  • The 'target_database_name' will be restored on the current server, overwriting any existing 'target_database_name'
  • The latest Full Backup for 'target_database_name' will be used, along with any Differential Backups and Log Backups that have been created between when the Full Backup was taken and the time FB_DBRestore is executed
  • The backup files will be searched for on 'server_hosting_backup'
  • A Linked Server definition must exist for 'server_hosting_backup' to allow the backup files to be found
  • If the database is part of an AG, all Secondaries will be re-seeded
  • The database will be left available for use

Restore a database that is part of an Availability Group

TBC

Restore a database to a specific point in time

TBC

Generate commands needed to restore a database

TBC

Restore database logs for a log-shipping situation

TBC

Group multiple data files into dedicated sub-folder

TBC

Apply specific Database Owner account to a restored database

See Also

Copyright FineBuild Team © 2019. License and Acknowledgements

Previous Proc FB_AGPostFailover Top Large Scale Deployment Using SQL FineBuild Next

Key SQL FineBuild Links:

SQL FineBuild supports:

  • All SQL Server versions from SQL 2019 through to SQL 2005
  • Clustered, Non-Clustered and Core implementations of server operating systems
  • Availability and Distributed Availability Groups
  • 64-bit and (where relevant) 32-bit versions of Windows

The following Windows versions are supported:

  • Windows 2022
  • Windows 11
  • Windows 2019
  • Windows 2016
  • Windows 10
  • Windows 2012 R2
  • Windows 8.1
  • Windows 2012
  • Windows 8
  • Windows 2008 R2
  • Windows 7
  • Windows 2008
  • Windows Vista
  • Windows 2003
  • Windows XP
Clone this wiki locally