Listing backup history

In this recipe, we will list the backup history for a SQL Server instance.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

   $instanceName = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
   
   #display date of last backup
   $server.Databases | 
   Select Name, RecoveryModel, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate | 
   Format-Table -AutoSize

   Your result should look similar to the following screenshot:

   

   Note that when you see a date of 1/1/0001 12:00:00 AM, then it means no backup has ever been taken for that database.

How it works...

Listing the backup history is a simple task, using a little bit of PowerShell and SMO. After you get a database handle, you can display the last backup dates...