SQL Server database backup – generating backup commands to multiple locations

Goal

To generate the backup commands for a full backup or a differential backup

My way of achieving it

Using xp_cmdshell and powershell. More often than we would like due to growth of the databases and creation of other databases the backup of our databases fails because of insufficient free space. Although most of the time the solution is to increase the disk where we save our backups there are exceptions from this rule

  • we really need to take a full backup or a differential one because we need to create a new log chain.
  • we really need to take a backup as a prerequisite of a change
  • we really need to take a full backup in order to free the space that other backups used to recover the database occupies on disk.  Some of the backup retention implementation will keep on disk all the backups since the last full backup.
  • we really need to take the backup according to the configured schedule, this schedule is created in such a way that will allow us to recover the database in some specific terms.

The script is generating the backup commands for locations found in backup history but also on the locations where SQL server keeps its database files.

The script requires

  • database name
  • backup type, full or differential
  • native or other type of backup, the script has syntax for litespeed but it can be modified to generate the commands using the syntax of your backup tool

The script identifies the size of the last full backup or differential one and uses that size as an estimation for the next backup.

The output of the script is shown below.

gbcmd

 

The script can be downloaded from here

 

Leave a Reply

Your email address will not be published. Required fields are marked *