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
Database Blog 