In my earlier PowerShell SQL Server article, SQL Database Backups using PowerShell Module – DBATools, we explored the importance of a disaster recovery solution for an organization. Microsoft offers various disaster recovery solutions in SQL Server.
- Database Backup and Restore
- Always on availability groups
- Failover Clustering
- Log Shipping
We will explore SQL database backups in this series. We learned to take database backups for SQL Server using PowerShell Module – DBATools. It is DBA responsibility to restore backups on source database instance or different instance as per requirement. We usually use SSMS restore database wizard or t-SQL scripts to restore the database. We need to know about backup related information before planning for database restore. We have various PowerShell commands in DBATools to get useful backup information such as backup history, back threshold.
In this article, we will discuss SQL database backup commands in PowerShell SQL Server module DBATools.
We can get information about commands related to any particular keyword using Get-Help command. In the following screenshot, you can all commands related to keyword backup.
Let us explore a few useful DBATools command before we start with SQL database restoration.
Find-DbaBackup
We can get a list of all database backup files placed in a directory using Find-DbaBackup command. It is good practice to get information about particular command before we start exploring it.
|
1 |
>get-help Find-DbaBackup |
Suppose I want to get a list of all SQL database backups in the default directory. We need to pass the following parameters in this command.
- -Path: Provide path of the directory in which we have placed SQL database backups
- -RetentionPeriod: It searches backup files older than the retention period
- -BackupFileExtension: We can search for files for a specific extension in a specified directory. We normally use .bak (full database backups) and *.trn (transaction log backup) extensions in SQL database backups
Before we execute Find-DbaBackup command, I have following backup files in default directory.
Run following command in PowerShell.
|
1 |
>Find-DbaBackup -Path 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019CTP\MSSQL\Backup\' -BackupFileExtension bak -RetentionPeriod 48h |
It returns all backup files having .bak extension and older than 48 hours.
Similarly, we can use following command to get backup in specified directory for .bak extension older than 3 days.
|
1 |
>Find-DbaBackup -Path 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019CTP\MSSQL\Backup\' -BackupFileExtension bak -RetentionPeriod 3d |
Measure-DbaBackupThroughput
It is an exciting and useful DBATools command to know SQL database backup performance. SQL Server stores backup information in the MSDB database. We can use DBATools function Measure-DbaBackupThroughput to give the following useful information.
- Minimum and Maximum Throughput
- Average backup duration
- First and last backup date
- Database backups count
- Average backup size
It is essential to understand database backup performance in SQL Server. We do not have any direct mechanism to calculate the details mentioned above. You have to get data from MSDB and perform calculations over it to get the required values.
DBATools provide Measure-DbaBackupThroughput to calculate these values for us without any additional configurations.
As usual, we need to check syntax for this command in PowerShell.
|
1 |
>Get-help Measure-DbaBackupThroughput |
We can have the following important parameters for this command.
- -SqlInstance: We need to give SQL instance name for which we want information about backup performance
- -Database: By default, it checks for all databases present in SQL instance. If we want details about a specific database, pass the database name to filter results
- -Last: We can use this parameter to get information about the last full, diff and log backups performance
- -Type: We can filter results for a particular backup type using this parameter
- -Since: We can use this filter to retrieve backup information from MSDB as per time filter using -Since parameter
Let us explore this function using an example.
In the following command, we want to check the SQL database backup throughput for SQL instance. I have used Out-GridView to display the result in a user-friendly format.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP | Out-GridView |
We have not used any filter in this command; therefore; it retrieves all database backup information from MSDB and calculates required information as shown in the following image. We get throughput information for our instance since initial database backup. It gives excellent information to check if we are not getting the desired throughput that might be the reason for a delay in backup completion. We have the following output using Measure-DbaBackupThroughput PowerShell command.
Let us retrieve throughput information for a single database using -database parameter.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP -Database SQLShackdemo | Out-GridView |
Suppose we do not want cumulative throughput information since first database backup. We only want to check throughput information for the last full backup of the specified database. We need to run the command with -Last and -Type (backup type) parameters.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP -Database SQLShackdemo -Last -Type Full | Out-GridView |
Similarly, we can use filter throughput information about last log backup for SQLDB database using the following command. I performed transaction log backup before running this command to get details in this demo.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP -Database SQLDB -Last -Type Log | Out-GridView |
We have not added any date or time filter until now. Suppose we want to analyze last 7 days of full database backups for SQLShackDemo database. We can add -Since parameter in the command.
In the following command, we filter last 7 days full backups using Get-Date and AddDays PowerShell function to filter results.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP -Since (Get-Date).AddDays(-7) | Out-GridView |
We can further filter results based on a threshold value. For example, we want to get backup details having a minimum throughput of less than 10 MB.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP -Since (Get-Date).AddDays(-7) | Where-Object { $_.MinThroughput.Megabyte -lt 10 } | Out-GridView |
Similarly, we can use the following query to get backup details having an average throughput of less than 5 MB in the last 7 days.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP -Since (Get-Date).AddDays(-7) | Where-Object { $_.AvgThroughput.Megabyte -lt 5 } | Out-GridView |
Normally, we do not care throughput of backups for system databases Master, Model and MSDB. We cannot have a backup for tempdb system database. We can exclude databases using – ExcludeDatabase parameter.
In the following query, we excluded system databases Master, Model and MSDB database from last 7 days backups having minimum throughput greater than 5 MB.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP -Since (Get-Date).AddDays(-7) -ExcludeDatabase master,model,msdb | Where-Object { $_.MinThroughput.Megabyte -gt 5 } | Out-GridView |
We can check SQL database Backups throughput for multiple instances altogether using Measure-DbaBackupThroughput.
Suppose we want to check backup throughput for following instances
- Kashish\SQL2019
- Kashish\SQL2019CTP
In the -SqlInstance parameter specify both instance name separated by a comma. Execute the following command.
|
1 |
>Measure-DbaBackupThroughput -SqlInstance Kashish\SQL2019CTP,Kashish\SQL2019 | Out-GridView |
We can see both instances of backup throughput in the following screenshot. Similarly, you can specify multiple instances in a single command to get consolidated output.
Get-DbaBackupHistory
We need to get backup history for SQL Server databases using Get-DbaBackuphistory PowerShell Module of DBATools. We get details about all backups belonging to a particular database or instance in SQL Server. You can use various customizations to retrieve the result set as per our requirement.
First, let us get information about Get-DbaBackuphistory using Get-Help
We usually use t-SQL to fetch details from MSDB to get the backup history of the SQL Server database. It requires you to join multiple tables and retrieve information with customization in the SQL script. DBATools helps us to view database history with minimum effort.
We can pass information about -SqlInstance parameter to get all backups information in the specified instance.
|
1 |
>Get-DbaBackupHistory -SqlInstance Kashish\SQL2019CTP | Out-GridView |
In the output, we get backup type, size, backup device type duration, start and end time.
Sometimes, we want latest backups (Full, Differential and Log backup) information in SQL instance. We can use –Last parameter for getting the latest backup information.
|
1 |
>Get-DbaBackupHistory -SqlInstance Kashish\SQL2019CTP -Last | Out-GridView |
If we have only full backup configured for a database, it just shows that entry. We have Full and Log backups for SQLDB database, therefore, in the output we can see both full and log backup.
We might have backups configured on disk, tape devices. We can filter results for a particular device type using -DeviceType parameter. I do not have any database backups in the Tape device. We can filter disk device backups for a particular database using the following command.
|
1 |
>Get-DbaBackupHistory -SqlInstance Kashish\SQL2019CTP -DeviceType Disk -Database SQLDB| Out-GridView |
Suppose, we want backup history after a specific time for SQLDB database in SQL instance. We can run the following command.
|
1 |
>Get-DbaBackupHistory -SqlInstance Kashish\SQL2019CTP -Database SQLDB -Since '2019-03-20 00:00:00' | Out-GridView |
Conclusion
In this article, we explored various SQL database backup options using DBATools PowerShell Module. It is an interesting module to explore for backups in PowerShell. In the next article, we will cover database restoration commands in DBATools.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023