SQL Database Backups using PowerShell Module – DBATools

This article will be first article of series for SQL database backup and restoration using DBAtools, a powerful open source library of PowerShell automation scripts.

Every organization should prepare a Disaster Recovery plan to avoid a business loss against any unexpected downtime. It can be in the form of power failure, data centre issues, cyber-attacks, natural disaster etc. We must do a regular disaster drill to be prepared any such incidents. The DBA plays a vital role in these drills.

We used to take regular database backups for production servers to restore later in case of any loss of data. You should do regular database restoration drills to verify that your backup policy is valid and able to recover from any incidents. We need to restore a database backup on the instance on the same version of SQL Server.

If you are maintaining a large number of production instances, it becomes tedious work to test backups on a specified frequency. We also need to perform additional task of consistency check using DBCC CHECKDB command after database restoration. It ensures that there is no allocation or consistency error in the restore database. As a summary, we need to perform two tasks in database restoration drills:

1. Restore database regularly from existing backups regularly
2. Database Consistency check on the newly restored database

In SQL Server, you can write t-SQL code or create an SSIS package to perform the required steps. You need to be good in programming or SSIS package development to do so.

As an alternative, we can use DBATools to maintain database backups, restoration and verifications for disaster recovery purpose. In this article, we will discuss database backups using PowerShell SQL Server module DBATools .

• Note: We are using Azure Data Studio to run DBATools commands. You can also use Windows PowerShell for this.

SQL Database backups using DBATools

We can get all commands related to keyword Backup using Get-help.

>Get-Help *Backup*

We will verify last database backups using command Get-DbaLastBackup. In this command, we used Out-GridView to get results in grid view.

>get-help Get-DbaLastBackup

In below screenshot, we can see the syntax, description for this command.

Let us run this command in my instance Kashish\SQL2019CTP . In the output, we can see that currently, I do not have any SQL backups for my database instance. It gives timestamp of each database backup in corresponding column such as LastFullBackup, lastDiffbackup, LastLogbackup. It also gives the details of the number of days since last full, differential and log backup.

Take a database backup using DBATools

We can perform database backups using Backup-DBADatabase command using DBATools in PowerShell SQL Server. Check the syntax of Backup-DBADatabase using below command.

>get-help Backup-DBADatabase

We can take full SQL database backups, transaction log backups, and database file backups. We need to provide the following parameters to take database backup.

• The server name in -parameter
• Database name to the -Database parameter
• Type of database backup using -Type parameter. If we do not specify any backup type, it takes Full backup
• We can specify a backup directory using -BackupDirectory parameter. If we do not specify any backup directory, it takes backup in the default directory
• We can specify a backup file name in –BackupDirectory parameter. By default, it takes backup as follows:
  
  1. Full backup: Databasename_yyyymmddhhss.bak
  2. Log backup: Databasename_ yyyymmddhhss.trn

Let us perform a database backup using Backup-DBADatabase by only specifying an instance name.

>Backup-DBADatabase -ServerInstance Kashish\SQL2019CTP

We did not specify any database in the Backup-DBADatabase command in PowerShell SQL Server. It takes backups of all databases in this case.

We can verify the database backup using Get-DbaLastBackup command. We can see here that entry for last full backup and status as well.

>Get-DbaLastBackup -SqlServer Kashish\SQL2019CTP | Out-GridView

If the database recovery model is full and log backups are not running, you can see in the status ‘No log backup in the last hour’.

In the default backup directory, database backup file is present. We can see the backup file in the format of databasename_yyyymmddhhss.bak as per default full backup format.

Differential database backup using DBATools

Suppose we want to take a differential backup for SQLShackDemo database only. We can specify the database name using -database parameter. We also need to specify a backup type as Differential.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo

It takes differential backup for the specified database and returns the details such as type, totalsize, start time, end time and duration for this backup.

Now, we want to retake differential SQL database backup for user databases SQLShackDemo and SQLShackDemo_ADR in the directory C:\TEMP\Backup. We can specify multiple databases name in -database parameter separated by a comma.

In the following query, we specified the backup directory in the BackupDirectory parameter.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo,SQLShackDemo_ADR -BackupDirectory C:\TEMP\Backup\

You can see the differential database backup for both the databases in the specified location.

Custom SQL database backup file format using DBATools

You might notice that backup file format is the same for both full and differential backup as database_YYYYMMDDHMM. We want to give a customized name for the backup file. We need to do following changes in command for backup using DBATools.

• BackupFileName: Specify a format for the database backup file. I want to include database name, backup type (Full, Differential, and Log) along with a timestamp of backup execution. For example, I will define the backup file name as dbname-backuptype-timestamp.bak
• ReplaceInName: We need to specify this switch in backup command to replace the strings in backupfilename with actual values

In this example, we are specifying BackupFileName as dbname-backuptype-timestamp.bak. Once we set ReplaceInName, command works as follows

• dbname – it replaces dbname with the actual database name
• timestamp – It specifies timestamp in BackupFileName
• backuptype – We get backup type in the BackupFileName

Run the following command in PowerShell SQL Server.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo  -BackupDirectory C:\TEMP\Backup -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName

Now look at the database backup directory and notice the backup file name.

We might also want to add SQL instance name in the backup file name; therefore, you can run the following command with instancename parameter in BackupFileName.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo  -BackupDirectory C:\TEMP\Backup - BackupFileName instancename-dbname-backuptype-timestamp.bak -ReplaceInName

We can see SQL instance name in a backup file as well.

Similarly, add a servername in a SQL database backup file name to specify server name as well.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo  -BackupDirectory C:\TEMP\Backup -BackupFileName servername-instancename-dbname-backuptype-timestamp.bak -ReplaceInName

SQL Database backups into a separate folder using DBATools

Suppose we want to create a separate folder for each database and all of its SQL database backups should go under it. It allows us to look for all database backups in a specified directory easily. If we have a large number of databases, it is not possible to manually create a folder and specify in backup command. DBATools solves these issues with -CreateFolder string. It automatically creates a folder for each database in a specified backup directory and takes backup of a particular database.

Run the following command to take all databases backup in SQL instance in a separate folder for each database.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP  -BackupDirectory C:\TEMP\Backup -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName -CreateFolder

Now look at the SQL database backup directory, and you can see a separate folder for each database. The SQL Database backup is also placed in a particular database folder.

If the folder already exists, it does not create a separate folder or overwrites it. It ignores step to create a directory and takes a backup in the existing directory only.

SQL Server supports backup compression. It is good practice to take compressed backup. We might have backup compression enabled at the instance level. We do not want to check compression configuration at instance level each time. We should specify compression in backup command as well to ensure the backup is compressed.

Compressed backup using DBATools

We can use -CompressBackup string to take compressed backup using DBATools command.

For this demo, I have taken a SQL database backup of SQLShackDemo with and without compression to show the difference.

• SQL database backup size with compression: 94.7 MB
• SQL database backup size without compression: 187 MB

Copy-Only SQL database backup using DBATools

We define a backup policy to take regular database backups. Sometimes we get the requirement for an ad-hoc backup. This ad-hoc backup might affect the backup LSN chain and we might need to reinitiate backup chain. SQL Server provides a solution in terms of copy-only backup. By default, DBATools takes a normal full backup of a database. We can specify CopyOnly string in backup command to take copy-only backup. It does not impact the LSN of database backups.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP  -BackupDirectory C:\TEMP\Backup -Database SQLShackDemo -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName  -CompressBackup -CopyOnly

SQL database backup verification and validation using DBATools

We want to perform database backup validation to avoid any corruption while writing backup in the media. It ensures that we have a compatible and verified backup file. SQL Server provides following backup validations.

• Perform CheckSum before writing to media
• Verify backup when finished

We can specify Checksum and Verify string to do these validations. We should add these strings to have a consistent backup and avoid any issues during database restoration.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP  -BackupDirectory C:\TEMP\Backup -Database SQLShackDemo -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName  -CompressBackup -CopyOnly -CheckSum –Verify

Transaction log backup using DBATools

We can specify -Type Log in backup command to take a transaction log backup.

>Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Log -Database msdb -BackupDirectory C:\TEMP\Backup\

Conclusion

In this article, we explored about database backups using PowerShell SQL Server module DBA-Tools. We can take backups multiple configurations and format using DBATools. We will cover database restoration with DBATools in my next article.

Table of contents

DBATools PowerShell Module for SQL Server PowerShell SQL Server Validation Utility – DBAChecks SQL Database Backups using PowerShell Module – DBATools