How to run native SQL backups across multiple servers and multiple databases

Follow the below process to run SQL backups across one or more SQL servers, one or more databases using a simple powershell command. This uses the dbatools, open source powershell modules. You would use this in a scenario when Commvault server is down.

Instructions

Run the backups using the following powershell script:

$servers = 'SQLSERVER01','SQLSERVER02'
$filepath = '\\NetworkPath\Backup\servername\dbname'
$dbnamelist = 'DB01','DB02'
foreach ($server in $servers)
{
Backup-DbaDatabase -SqlInstance $server -database $dbnamelist -BackupDirectory $filepath -Type Full -CompressBackup -ReplaceInName -BuildPath
}

In the example above, the script would backup databases named ‘DB01′ and ‘DB02’ on both servers listed in the $servers variable. In order to backup all the databases, remove the -database parameter.

Creating Directories Dynamically

In the previous examples we just wrote the backups to the root of a file share. But sometimes you’ll want to organize or separate your backups based off of server name or database name or backup type. This is something else that dbatools makes very simple to do dynamically. This is done by using the –ReplaceInName parameter and then specifying keywords in your FilePath and Path strings.

-ReplaceInName
If this switch is set, the following list of strings will be replaced in the FilePath and Path strings:
instancename – will be replaced with the instance Name
servername – will be replaced with the server name
dbname – will be replaced with the database name
timestamp – will be replaced with the timestamp (either the default, or the format provided)
backuptype – will be replaced with Full, Log or Differential as appropriate

The backup jobs have already been created for certain critical servers which will enable us to switch to an alternative way to keep running backups in case Commvault server goes down.

Run the below script to enable the backup jobs across multiple servers:

# Run the following to enable native SQL backups to a network path
#Backup Location = '\\NetworkPath\Backup\servername\dbname'
$servers = 'SQLSERVER01','SQLSERVER02','SQLSERVER03'
foreach($server in $servers)
{
Set-DbaAgentJob -SqlInstance $server -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL', 'DatabaseBackup - USER_DATABASES - DIFF', 'DatabaseBackup - USER_DATABASES - FULL' -Enabled
}

# Run the following to enable transaction log backups for selected servers
Set-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'DatabaseBackup - SYSTEM_DATABASES - LOG' -Enabled

 Run the below script to disable the backup jobs across multiple servers:

# Run the following to disable native SQL backups 
$servers = 'SQLSERVER01','SQLSERVER02','SQLSERVER03'
foreach($server in $servers)
{
Set-DbaAgentJob -SqlInstance $server -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL', 'DatabaseBackup - USER_DATABASES - DIFF', 'DatabaseBackup - USER_DATABASES - FULL' -Disabled
}
# Run the following to disable transaction log backups on selected servers
Set-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'DatabaseBackup - SYSTEM_DATABASES - LOG' -Disabled

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: