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:
- Restore database regularly from existing backups regularly
- 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.
We will verify last database backups using command Get-DbaLastBackup. In this command, we used Out-GridView to get results in grid view.
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.
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:
- Full backup: Databasename_yyyymmddhhss.bak
- 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\
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
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at firstname.lastname@example.org
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- An overview of native differential backups in AWS RDS SQL Server - December 9, 2019
- An overview of SQL Server data types - December 9, 2019
- Sequence Containers in SSIS packages - December 6, 2019