Total Pageviews

Thursday, August 4, 2016

PowerShell Way: Backing up numerous databases (Full, Differential, Transaction Log) on numerous SQL Server Instances concurrently and intelligently

Do you want to back up databases on multiple servers simultaneously from a central server without deploying any code with an edge of efficiency and in an intuitive way? The purpose of this PowerShell Process development initiative is to backup hundreds of databases that reside on hundreds of database servers with different backup strategies.

Brief description of the Script:
The PowerShell function “Do_dbBackup” in the script is a work-flow type function and it utilizes multi-threading mechanism of the PowerShell Process. It requires two input parameters: one is a list of database servers which contains server information along with various backup options and another is error output file for error logging.

Following are some highlights of this process:
1.      Performing Full or Differential backup.
2.      Dumping the backup files to a local or network shared folder.
3.      Will create a folder automatically if it does not exist.
4.      Deleting old backups file based on retention period.
5.      Utilizing backup compression for Enterprise Edition.
6.      Error handling, logging and notification.
7.      Performing system database backups.
8.      Differential or T-Log backup will not happen if there is no prior full backup of a database.

Parameters found in the configuration file are adjustable to fit a specific requirement. This parameter file can be used for backing up a Transaction Log as well.

How to use the Script?
The script assumes that the executer (i.e. the domain account) of this script has permission to access all target database servers as “sysadmin” and has read\write permission for the Windows drive, Local folder or network shared folder.

The PowerShell script “DBBackupV2.ps1” contains the main function “Do_dbBackup” and it accepts two parameters as follows:

1.      $ServerListPath = list of database instances along with various options
2.      $ErrorLogFile = Error logging file

At the bottom of the script, the “Do_dbBackup” is called as follows:

$DBConfigFile = "D:\Work2016\PSScript2016\DBBackupV1\ServerList.txt"
$LogFile = 'D:\Work2016\PSScript2016\DBBackupV1\ErrorLog.txt'

Invoke-Command -ScriptBlock { Do_DBBackup -ServerListPath $DBConfigFile -ErrorLogFile $LogFile }

How to execute the Script?
The “DBBackupV2.ps1” or the “LogBackupV1.ps1” can be executed either by a Windows Task, by a SQL Server Agent or manually. Here is a simple example of using the SQL Server Agent to execute the script.


 Description of parameters:
The “ServerList.txt” is a CSV type file which can hold several database instances and corresponding  configuration options for each server. The Header of the CSV is the following.

BackupYesNo, Description, SQLInstance, FullBakPath, DiffBakPath, TLogBakPath, SysBakPath, FullBakRetentionHour, DiffBakRetentionHour, TLogBakRetentionHour, SysBakRetentionHour, day1, day2, day3, day4, day5, day6, day7

·         The first row represents the header of each column.
·         The column “BackupYesNo” is for whether a database backup will be performed or not. Value is “Yes” or “No”
·         The second column “Description” is for Database Server description. e.g. “Payment Processing Server”
·         The Third column is for SQL Server Instance name.
·         FullBakPath, DiffBakPath, TLogBakPath, SysBakPath column represents the destination folder of backup.
·         FullBakRetentionHour, DiffBakRetentionHour, TLogBakRetentionHour, SysBakRetentionHour represents the backup retention hours respectively.
·         day1, day2, day3, day4, day5, day6, day7 represents the day name and backup type. The format should be similar to Sunday\FULL, Monday\Full, Tuesday\Full, Wednesday\DIFF, Thursday\Full, Friday\Full, Saturday\DIFF

Transaction Log Backup:
The “Do_LogBackup” in the “LogBackupV2.ps1” Scripts uses the same configuration file; however, the job needs to be scheduled in regular intervals. The process also creates folder automatically if required and deletes old t-log backup files.

Download Link:

Script download link: http://bit.ly/2aSTbhz

The shared link contains two scripts: one for Database backup and another for Log backup. Download then extract all three files in a folder. Change the CSV file path and adjust configuration parameters according to your needs.


Review this script, test it and enhance it to incorporate your own ideas and requirement.