Proc FB_DBRestore

Previous Proc FB_AGSystemData	Table FB_AGPostFailoverDBUsers Next

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

• Restore a database to the same name and server as the original
• Restore a database to a different name from the original
• Restore a database to a different location from the original
• Restore a database to a different server from the original
• Restore a database to a specific point in time
• Restore database logs for a log-shipping situation
• Prevent Re-Seed of a database that is part of an Availability Group
• Generate commands needed to restore a database
• Group multiple data files into dedicated sub-folder
• Apply specific Database Owner account to a restored database

The rest of this page contains the following sections

• Syntax
• Argumements
• Return Code Values
• Result Sets
• Remarks
• Cross-Server Restore
• Permissions
• Examples
• See Also

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

  marker_for_restore_end can be either a canonical date/time (yyyy/mm/dd hh:mm:ss.nnn) or a log mark name.

• [ , [ @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 the following table will be used 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

A return code of 0 means success. Any other value means failure.

Result Sets

• If a Restore statement can be generated it will be printed. It will also be returned in the @SQLRestore OUTPUT variable
• If a Restore statement can not be generated an error message will be printed

Remarks

TBC

Cross-Server Restore

TBC

Permissions

FB_DBRestore can only be run by a user with sysadmin privileges

Examples

This section contains the following examples:

• Restore a database to the same name and server as the original
• Restore a database to a different name from the original
• Restore a database to a different location from the original
• Restore a database to a different server from the original
• Restore a database to a specific point in time
• Restore database logs for a log-shipping situation
• Prevent Re-Seed of a database that is part of an Availability Group
• Generate commands needed to restore a database
• Group multiple data files into dedicated sub-folder
• Apply specific Database Owner account to a restored database

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 to a specific point in time

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@Stopat  =      'marker_for_restore_end'
   ,@Execute =      'Y'

• The 'target_database_name' will be restored on the current server, overwriting any existing 'target_database_name'
• The log file containing the 'marker_for_restore_end' will be identified
• The most recent Full Backup for 'target_database_name' prior to the 'marker for restore_end' will be used, along with any Differential Backups and Log Backups that have been created between when the Full Backup was taken and the 'marker_for_restore_end' value
• If the database is part of an AG, all Secondaries will be re-seeded
• The database will be left available for use

Restore database logs for a log-shipping situation

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@FullApply =    'N'
   ,@Recovery  =    'N'
   ,@Standby   =    'Y'
   ,@Execute   =    'Y'

• If 'target_database_name' does not already exist it will be restored on the current server. 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 'target_database_name' does already exist and is in NORECOVERY mode it will be rolled forward. Any Differential Backups and Log Backups taken since the last roll-forward will be applied
• The database must not be part of an AG
• The database will be left in STANDBY mode, available for read-only enquiries

Prevent Re-Seed of a database that is part of an Availability Group

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@SetupReseed =  'N'
   ,@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 become frozen in time and isolated from the Primary
• The database will be left available for use

Generate commands needed to restore a database

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'

• The command to restore 'target_database_name' will be printed
• No restore will be performed

Group multiple data files into dedicated sub-folder

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@DBFolder =     'Y'
   ,@Execute  =     'Y'

• The 'target_database_name' will be restored on the current server, overwriting any existing 'target_database_name'
• A sub-folder of the default data path will be created for the database data files
• If the database is part of an AG, all Secondaries will be re-seeded
• The database will be left available for use

If a database contains multiple data files then by default a sub-folder will be created to hold these

Apply specific Database Owner account to a restored database

EXEC master.dbo.FB_DBRestore @DatabaseName = 'target_database_name'
   ,@DBOwner  =     'owner_account'
   ,@Execute  =     'Y'

• The 'target_database_name' will be restored on the current server, overwriting any existing 'target_database_name'
• The database owner will be set to the specified 'owner_account' at the end of the restore
• If the database is part of an AG, all Secondaries will be re-seeded
• The database will be left available for use

If no @DBOwner parameter is supplied, the database owner will be set as described in Arguments

See Also

• RESTORE Statements (T-SQL)

Copyright FineBuild Team © 2019-2020. License and Acknowledgements

Previous Proc FB_AGSystemData	Top	Table FB_AGPostFailoverDBUsers Next