Rajendra Gupta
Backup SQL database - Database backs into a separate folder using DBATools

SQL Database Backups using PowerShell Module – DBATools

March 21, 2019 by

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.


Backup SQL database - Database backups using DBATools

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.

Backup SQL database - last database backups using command Get-DbaLastBackup.

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.

Backup SQL database - last database backups using command Get-DbaLastBackup.

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.


Backup SQL database - Take database backup using DBATools

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 SQL database - Take database backup using DBATools

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.

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’.

Backup SQL database - Take database backup using DBATools

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.

Backup SQL database - Take database backup using DBATools

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.

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.

Backup SQL database - Differential backup using DBATools

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.


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.


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.


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.


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 SQL database - Database backs into a separate folder using DBATools

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.

Backup SQL database - Database backs into a separate folder using DBATools

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.

Backup SQL database - Database backs into a separate folder using DBATools

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 SQL database - Copy-Only  backup using DBATools

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 SQL database - Backup verification and validation using DBATools

Transaction log backup using DBATools

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


Backup SQL database - Transaction log backup using DBATools

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
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views