Rajendra Gupta
PowerShell SQL Server - Get-DbaBackupHistory PowerShell Command Examples SQL Server

DBATools PowerShell SQL Server Database Backups commands

March 26, 2019 by

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.

PowerShell SQL Server - SQL Database Backups commands in DBATools

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.


PowerShell SQL Server - Find-DbaBackup PowerShell Command

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.

PowerShell SQL Server - Find-DbaBackup PowerShell Command

Run following command in PowerShell.

It returns all backup files having .bak extension and older than 48 hours.

PowerShell SQL Server - Find-DbaBackup PowerShell Command configurations

Similarly, we can use following command to get backup in specified directory for .bak extension older than 3 days.


PowerShell SQL Server - Find-DbaBackup PowerShell Command configurations

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.


PowerShell SQL Server - Measure-DbaBackupThroughput command in PowerShell

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.

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.

PowerShell SQL Server - Measure-DbaBackupThroughput command in PowerShell

Let us retrieve throughput information for a single database using -database parameter.


PowerShell SQL Server - Measure-DbaBackupThroughput command examples  in PowerShell

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.


PowerShell SQL Server - Measure-DbaBackupThroughput command examples  in PowerShell

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.


PowerShell SQL Server - Measure-DbaBackupThroughput command examples  in PowerShell

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.


PowerShell SQL Server - Measure-DbaBackupThroughput command examples  in PowerShell

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.


PowerShell SQL Server - Measure-DbaBackupThroughput command examples  in PowerShell

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.


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.


PowerShell SQL Server - Measure-DbaBackupThroughput command examples  in PowerShell

We can check SQL database Backups throughput for multiple instances altogether using Measure-DbaBackupThroughput.

Suppose we want to check backup throughput for following instances

  1. Kashish\SQL2019
  2. Kashish\SQL2019CTP

In the -SqlInstance parameter specify both instance name separated by a comma. Execute the following command.

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.

PowerShell SQL Server - Measure-DbaBackupThroughput command examples for multiple instances  in PowerShell

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

PowerShell SQL Server - Get-DbaBackupHistory PowerShell Command SQL Server

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.

In the output, we get backup type, size, backup device type duration, start and end time.

PowerShell SQL Server - Get-DbaBackupHistory PowerShell Command Examples SQL Server

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.

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.

PowerShell SQL Server - Get-DbaBackupHistory PowerShell Command Examples SQL Server

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.


PowerShell SQL Server - Get-DbaBackupHistory PowerShell Command Examples SQL Server

Suppose, we want backup history after a specific time for SQLDB database in SQL instance. We can run the following command.


PowerShell SQL Server - Get-DbaBackupHistory PowerShell Command Examples SQL Server

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

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

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

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 rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
1,825 Views