Skip to content

BackupAndRestore

Jump to bottom
MikeyMing edited this page Dec 8, 2020 · 27 revisions

Quickstart

Copy the DBALibrary.ps1 file to a location locally and 'dot source' it to enable the functions. e.g.

. "c:\PowerShellScripts\DBALibrary.ps1"

Then, from a PowerShell window, execute the function. e.g.

BackupAndRestore -SourceInstance SOURCEINSTANCE -SourceDatabase SOURCEDB -TargetInstance TARGETINSTANCE -TargetDatabase TARGETDB -CreateDatabase $True -CompatabilityLevel SetLatest -RecoveryModel SIMPLE -BackupPath \\server\share

Please contact me at mikefle@gmail.com with any queries.

Overview

This routine will take a copy of a database from the source server and restore it onto the specified destination server. This is a very common requirement for a DBA and is a manually intensive process that ought to include a lot of checks and configurations to the restored database.

Some of the frustrations of a manual refresh process are

  • it being a resource intensive process. One has to 'babysit' the different stages which takes up time, especially on larger databases. One has to check
  • the process failing half way through. This could be because of lack of space on the target machine, the wrong version of SQL, a missing certificate on the target. It would be more efficient if these scenarios were checked at the start so any problems could be identified and rectified.
  • It is easy to miss out critical steps, e.g. not setting the compatibility level to match the new version, not setting the recovery model correctly, having an incorrect database owner are all common issues when refreshes are done manually

Prerequisites

The library is designed to as flexible as possible. Environment dependent features are options, but functionality will be enhanced where they are included.

  • There needs to be file share area that is accessible to both SQL Servers service accounts with read and write permissions. The operator also need read and write to this area.
  • The operator needs to have sysadmin on both instances.
  • Optionally, for completion emails, an SMTP server with access to send mails for the operator. If the operators account is furnished with an email address in AD then this will be used, otherwise an email address can be supplied as a parameter.
  • Optionally, for logging to a database table, the log table needs to be created, and the $DBAInstance and $DBADatabase variables need to be set. See the page on creating the logging table and procedure for more information. If these are not set the script will continue but logging to a table will not be enbabled.
  • Optionally, the SMTP server needs to be set by furnishing the $SMTPServer variable in the script.
  • The library needs to be loaded. This is done by 'dot sourcing' the ps1 file as follows

. "\\SERVER\Share\Path\DBALibrary.ps1"

Description of the Process

Checks

It will check all requirements before hand so that the operation can be done in one command and any errors will most likely be flagged straight away rather than for example when the target is being restored.

Some of the checks that are done are

  • checks space on the target drive
  • Checks if the required TDE certificate is installed on the target server
  • checks that the target server is not a lower version than the source server, and that the versions are not too far apart to allow a restore
  • Checks if the specified database is part of an AlwaysOn group
  • Checks that the database does not exist if the CreateDatabase parameter is not set to true

Parameters

It will then continue to restore the database. The parameters relevant to this stage are as follows

  • -SourceInstance, -SourceDatabase, -TargetInstance and -TargetDatabase provide the details for source and target databases (required)
  • -BackupLocation This is an area on the network which is accessible to both source and target instances where the backup files are created and restored from. (required)
  • -CompatabilityLevel One of ('SetLatest','Retain','100','110','120','130','140') where 'SetLatest' will set to the latest available on the target server, and 'Retain' will preserve the value from the source server. (required)
  • -RecoveryModel One of ('SIMPLE','FULL','Retain') where 'Retain' will copy the value from the source database (required)
  • -BatchMode This allows the script to continue silently without any prompts.
  • -Differential allows a backup to be applied with the NORECOVERY option so that a differential backup can then be applied to update any changes. This is useful when migrating large databases and minimizing the downtime on the source server.
  • -CopyOnly This will mark the backup as copy only, so differentials are not affected. This is the default
  • -WaitForManualRestore This will allow the user to benefit from the automation of the script, but allow a BAK file location to be supplied
  • -DontCheckSpace This skips the space check on the target instance. It is useful for testing
  • -IntermediateInstance If specified, the database will be copied to the instance specified before being subsequently backed up again and restored onto the target instance. This is useful when migrating from a source version that is too far behind the target instance version and a direct upgrade path is not supported -DontBackupTargetInstance By default the script will take a backup of the target database concurrently with the source database.
  • -MarkAsRetain Will label the backup filename to include the word 'retain' This would be useful when doing a migration so that a final archive from the source system is not removed (a check for the word 'retain' could be made in the removal script)
  • -NumberOfBackupsToRetain Will delete the previous backups, leaving the number specified. Will delete the target database backups only if -DontBackupTargetInstance is false.
  • -MaxTransferSize Sent as a WITH parameter to the BACKUP command. Values are environment specific and can have a significant effect on performance.
  • -BufferCount Sent as a WITH parameter to the BACKUP command. Values are environment specific and can have a significant effect on performance.
  • -BlockSize Sent as a WITH parameter to the BACKUP command. Values are environment specific and can have a significant effect on performance.

Configurations

Some configurations are then applied

  • It will set the owner, depending on the value of the -RetainOwnerName parameter
  • It will rename the files according to the target database name. It will do this by finding the common part of the name, e.g. if source database is call dbProd with datafile dbprod.mdf then target database with name dbDev will have a datafile called dbDev.mdf. The location of the files is based on the default paths on the instance.
  • It will run a DBCC check, unless the -NoDBCC parameter is supplied
  • If -CopyUserRoles is specified, it will reinstate any roles that were the target database before it was refreshed from the source
  • If -CreateLoginsIfTheyDoNotExist is specified, it will create any required logins for that database from the source instance
  • If -ChangeCollation parameter is specified, it will change the collation of any objects that do not match the collation specified to the value specified in the -Collation parameter.
  • If -ShrinkLog is specified, it will shrink the log on the target database
  • If -UpdateStats is specified, it will run stats update on target
  • It will run any script specified in the -ScriptToRunOnTarget parameter
  • It will apply the value of the Trustworthy bit from the source instance

Examples

This command will replicate a database from one server to another

BackupAndRestore -SourceInstance SOURCEINSTANCE -SourceDatabase SOURCEDB -TargetInstance TARGETINSTANCE -TargetDatabase TARGETDB -CreateDatabase $true -CompatabilityLevel SetLatest -RecoveryModel SIMPLE -BackupPath \\server\share

It assumes that the target server does not exist.

The following does similar, except it is overwriting an existing database on the target:

BackupAndRestore -SourceInstance SOURCEINSTANCE -SourceDatabase SOURCEDB -TargetInstance TARGETINSTANCE -TargetDatabase TARGETDB -CreateDatabase $False -CompatabilityLevel SetLatest -RecoveryModel SIMPLE -BackupPath \\server\share -OverwriteTarget $true -TakeTargetOffline $true

Note that the -CreateDatabase parameter has been changed to $False and -OverwriteTarget $true -TakeTargetOffline $true have been specified.

In the following example, the target machine is out of space on the destinations drives, a warning is displayed. The user can select Y to continue, or can use the -BatchMode parameter to prevent the prompts.

When the backup and restore is in progress, a progress bar will be displayed

When the operation is complete, an email will be sent to the operator. This will give a summary of the parameters used, and will mdisplay the log, if logging is enabled. Is there was an error, this will be indicated, and the error message will be displayed.

Clone this wiki locally