SQL Server is one of the most stable and widely used database engines around in corporate environments. Given that SQL Server 2008 R2 Express+ is available free of charge, is rock solid and can support databases up to 10GB in size, it's a logical choice for many web and desktop applications hosted in-house in corporate environments. 

What can often be overlooked until it's too late (i.e. malware has corrupted the database, ransomware has encrypted it or the hard disk has given up the ghost) is a robust backup strategy. Commercial SQL Server backup tools can cost thousands but our method of choice is a robust Windows Powershell script. This script:

• Works its way across the network
• Pings instances to make sure they're online
• Connects to those instances (it uses Windows Authentication so make sure it's run under an account with suitable privileges - or, the script could easily be modified to use a SQL Server login)
• Loops through the databases in that instance
• Runs a DBCC CHECKDB command to ensure the database is OK
• Backs each database up in turn to a destination specified by a configuration file
• Verifies that the database can be restored (i.e. that the backup is valid)
• Generates a 7-zip backup for each machine it backed up data from
• Verifies that the compressed archive is valid
• and finally, it emails the log file to the system administrator at the end of the process

The Powershell code looks as follows:

# Settings $working_path = "C:\PSBackup\" # Log file $logfile = "$($working_path)sql_server_backup.log" # Load snap-in if((Get-PSSnapin -Name "*SQL*" -ErrorAction SilentlyContinue) -eq $null) { Add-PsSnapin "*SQL*" } # Email notification on failure function email-admin($body) { $mail = New-Object System.Net.Mail.MailMessage $mail.From = "my.sending.email.address" $mail.To.Add("my.receiving.email.address") $mail.Subject = "Database Backup and Monitor" $mail.Body = $body $smtp = New-Object System.Net.Mail.SmtpClient $smtp.Host = "my.smtp.server.com" $smtp.Credentials = New-Object System.Net.NetworkCredential("my.sending.email.address", "my.password") $smtp.Send($mail) } # Function to format file size function SQL-Format-File-Size { param ( [parameter(Mandatory = $true)][string] $file ) switch((Get-Item $file).length) { { $_ -gt 1tb } { $rv = "{0:n2} TB" -f ($_ / 1tb) } { $_ -gt 1gb } { $rv = "{0:n2} GB" -f ($_ / 1gb) } { $_ -gt 1mb } { $rv = "{0:n2} MB" -f ($_ / 1mb) } { $_ -gt 1kb } { $rv = "{0:n2} KB" -f ($_ / 1Kb) } default { $rv = "{0} B" -f $_} } return $rv } # Function to ping SQL instance function SQL-Ping-Instance { param ( [parameter(Mandatory = $true)][string] $ServerInstance, [parameter(Mandatory = $false)][int] $TimeOut = 1 ) $PingResult = $false try { $SqlCatalog = "master" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $ServerInstance; Database = $SqlCatalog; Integrated Security = True; Connection Timeout=$TimeOut" $SqlConnection.Open() $PingResult = $SqlConnection.State -eq "Open" } catch { } finally { $SqlConnection.Close() } return $pingResult } # Init $date = Get-Date -Format "dd/MM/yyyy HH:mm" echo "-----------------------" | Out-File -Append $logfile echo "Init @ $date" | Out-File -Append $logfile echo "-----------------------" | Out-File -Append $logfile # Get backup path $directory = Get-Content "$($working_path)sql_server_backup_path.txt" # Get machine/server names $machines = Get-Content "$($working_path)sql_server_backup_servers.txt" # Get exclude list $exclude = Get-Content "$($working_path)sql_server_backup_exclude.txt" # Beancounter $machine_count = 0 $instance_count = 0 $database_count = 0 $excl_instance_count = 0 $excl_database_count = 0 $send_mail = 0 # Loop servers foreach($machine in $machines) { echo "Machine [$($machine)]" | Out-File -Append $logfile $machine_count++ $instances = dir "SQLSERVER:\SQL\$($machine)" | Select Name foreach($instance in $instances) { echo " - Instance [$($instance.Name)]" | Out-File -Append $logfile if(SQL-Ping-Instance $instance.Name) { echo " --> Pingable" | Out-File -Append $logfile if($exclude -notcontains $instance.Name) { echo " --> Include in backup plan" | Out-File -Append $logfile $instance_count++ $instance_name_to_query = $instance.name if($instance_name_to_query -eq $machine) { $instance_name_to_query = $instance_name_to_query + "\DEFAULT" } $databases = dir "SQLSERVER:\SQL\$($instance_name_to_query)\Databases" | Select Name foreach($database in $databases) { if($database.Name) { echo " --> Database [$($database.Name)]" | Out-File -Append $logfile if($exclude -notcontains "$($instance.Name)\$($database.Name)") { $bakpath = $directory + "\" + $instance.Name $bakfile = $bakpath + "\" + $database.Name + ".bak"; echo " --> Backup path [$bakfile]" | Out-File -Append $logfile if((Test-Path -PathType Container $bakpath) -eq 0) { New-Item -ItemType Directory -Force -Path $bakpath | Out-Null echo " --> Creating directory [$bakpath]" | Out-File -Append $logfile } $CheckDbWatch = [System.Diagnostics.Stopwatch]::StartNew() echo " --> Checking database consistency $($database.Name)" | Out-File -Append $logfile Start-Transcript -path "$($working_path)db.tmp" Invoke-Sqlcmd -Verbose -QueryTimeout 65000 -ErrorAction Stop -ServerInstance $($instance.Name) -SuppressProviderContextWarning -Query "DBCC CHECKDB ($($database.Name))" Stop-Transcript $output = [IO.File]::ReadAllText("$($working_path)db.tmp") $CheckDbWatch.Stop() echo " --> Database $($database.Name) integrity check done in $($CheckDbWatch.Elapsed.ToString()) [hh:mm:ss.ddd]." | Out-File -Append $logfile if ($output -like '*CHECKDB found 0 allocation errors and 0 consistency errors in database*') { echo " [o] Database $($database.Name) integrity check is OK" | Out-File -Append $logfile } else { echo " [x] Error in integrity check of the database $($database.Name). $($output)" | Out-File -Append $logfile $send_mail = 1 } echo " --> Backing up database $($database.Name)" | Out-File -Append $logfile try { Invoke-Sqlcmd -QueryTimeout 65000 -ErrorAction Stop -ServerInstance $($instance.Name) -SuppressProviderContextWarning -Query "BACKUP DATABASE $($database.Name) TO DISK=N'$bakfile' WITH INIT, CHECKSUM"; $filesize = SQL-Format-File-Size $bakfile echo " [o] Backed up database $($database.Name) [$($filesize)]" | Out-File -Append $logfile $database_count++ try { Invoke-Sqlcmd -QueryTimeout 65000 -ErrorAction Stop -ServerInstance $($instance.Name) -SuppressProviderContextWarning -Query "RESTORE VERIFYONLY FROM DISK=N'$bakfile'"; echo " [o] Verified database $($database.Name)" | Out-File -Append $logfile } catch { echo " [x] Error verifying database $($database.Name). " + $_ | Out-File -Append $logfile $send_mail = 1 } } catch { echo " [x] Error backing up database $($database.Name). " + $_ | Out-File -Append $logfile $send_mail = 1 } } else { echo " --> Exclude from backup plan" | Out-File -Append $logfile $excl_database_count++ } } } } else { echo " --> Exclude from backup plan" | Out-File -Append $logfile $excl_instance_count++ } } else { echo " --> Not pingable" | Out-File -Append $logfile } } $cmd = "$($working_path)7za.exe u '$($working_path)$($machine).7z' '$($working_path)$($machine)'" Invoke-Expression $cmd echo "[o] Compressed Machine [$($machine)] Databases into [$($working_path)$($machine).7z]" | Out-File -Append $logfile $cmd = "$($working_path)7za.exe t '$($working_path)$($machine).7z'" $output = Invoke-Expression $cmd if($output -like '*Everything is Ok*') { echo "[o] Archive OK [$($working_path)$($machine).7z]" | Out-File -Append $logfile } else { echo "[x] Archive corrupt [$($working_path)$($machine).7z]" | Out-File -Append $logfile $send_mail = 1 } } echo "Backed up [$database_count] database(s) across [$instance_count] instance(s) on [$machine_count] machine(s). Skipped [$excl_instance_count] instance(s), [$excl_database_count] database(s)" | Out-File -Append $logfile echo "" | Out-File -Append $logfile if($send_mail -eq 1) { ## Email the log [void][System.Reflection.Assembly]::LoadWithPartialName("System.Net.Mail") email-admin([IO.File]::ReadAllText($logfile)) }

You'll notice that a few configuration files adjust the way the script functions. Specifically, sql_server_backup_path.txt will simply contain the path (local to the SQL Server instance) to backup the databases to - in our example, we've set it to c:\PSBackup. sql_server_backup_servers.txt contains the list of machines that we should connect to, in this example, I'm connecting to 2 local machines and one remote machine:

REKII
REKIII
REKRMVI


Finally, sql_server_backup_exclude.txt contains a list of machines, instances or databases that should be excluded from the backup plan, i.e. in our example, we don't want to backup any databases from the IMF instance on REKII and we don't want to backup the country_data database in the BIS instance on REKIII:

REKII\IMF
REKIII\BIS\country_data


There are a couple of dependencies for all aspects of this script to work, i.e. you need the Powershell SQL snap-ins, the 7-zip command line utility and you should enter in your SMTP server and account credentials so you get the summary log email. I hope it offers at the very least a solid foundation for implementing a commercial SQL Server backup strategy.

As with all articles on Celtic Productions, this article is protected by international copyright laws. It may be linked to (we are of course most grateful of links to our articles), however, it may never be reproduced without the prior express permission of its owners, Celtic Productions. All code is provided as-is, without warranty of any kind, either express or implied.