Month: December 2020

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

This script will download and install the latest version of SQL Server Maintenance Solution created by Ola Hallengren

Install-DbaMaintenanceSolution -sqlinstance SQLSERVER01 -database master -BackupLocation "\\NetworkPath\Backup\SQLSERVER01" -InstallJobs  -CleanupTime 168

What do the parameters mean?

Following script will remove the unwanted SQL agent jobs created by the maintenance solution.

We delete the below four jobs since we handle the requirement through different jobs

Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'sp_purge_jobhistory' -Mode Lazy
Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'sp_delete_backuphistory' -Mode Lazy
Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'CommandLog Cleanup' -Mode Lazy 
Remove-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'Output File Cleanup' -Mode Lazy 

Highlight important information in a panel like this one. To edit this panel’s color or style, select one of the options in the menu below.