Use PowerShell to Report SQL Server Backup Status

Use PowerShell to Report SQL Server Backup Status

  • Comments 5
  • Likes

Summary: Learn how to use Windows PowerShell to automatically email a report on SQL Server backup status.

Hey, Scripting Guy! Question Hey, Scripting Guy!

—CN

Hey, Scripting Guy! Answer Hello CN, Microsoft Scripting Guy, Ed Wilson, here. The time is near for SQLRally in Orlando. I will be making a presentation about Windows PowerShell and I wanted to support the rally. I thought the best way to do that would be to have a week of SQL blog posts and asked my friend, Microsoft SQL MVP Aaron Nelson (aka SQLVariant) if he had anything to share. Boy, did he come through! He rounded up four guest bloggers, and I rounded out the week with a newcomer to our ever growing circle of scripters. First up is Nicholas Cain.

Nicholas Cain is a senior SQL Server DBA with over 10 years experience. He has a great deal of experience working in large enterprises with databases ranging in size from a few MB to multi-TB. He holds MCITP certifications in both development and database administration with SQL Server 2008. A fan and speaker at SQLSaturday events, Nicholas really enjoys the community aspects of SQL Server, and he can be found on Twitter as @AnonyTheMouse and at his blog English to SQL.

Checking SQL Server backups

A couple of years ago, I moved from New Jersey to Washington State, and I was fortunate enough to work at a location that was not based in one of the major metropolitan areas. I recently changed jobs. One of the saddest parts of the change for me was not being able to look out the office window to get a clear view of the Cascade Mountains.

Photo of mountain view

A view like this does not cost anything—although I wish I had a dollar for every time I have been asked over the last 10 years when a database was last backed up. I would not be a rich man; however, my income would be supplemented nicely.

Backups are vital for any business, and databases frequently keep the business running. Imagine your database server goes down, and on that server is your customer list and all invoice information. Without a backup, you cannot recover the database and your business goes under. You need to know that your databases are being backed up. You also need to be able to quickly check and confirm that every database on every server is being backed up.

It is a challenge for any SQL Server DBA to have this information at hand, and if you are only a part-time or accidental DBA, that difficulty can easily be exponentially worse. For a single server, this is relatively simple. But if you are responsible for ten, twenty, fifty, or more SQL instances, you’ll find yourself very quickly overwhelmed. (A SQL instance is a defined individual installation of the SQL Server services. You can have multiple instances on a physical server—one can be a default instance, and the others have to be named. See Working with Multiple Versions and Instances of SQL Server on MSDN for more details).

So how can I check my backups?

To the rescue come Windows PowerShell and the SQL snap-ins. So much information about SQL instances, databases, tables, procedures, etc., is quickly and easily exposed through these snap-ins. They really make the life of the SQL Server DBA such a more pleasant experience.

You are going to want to do this on a machine with the SQL snap-ins installed. The quickest and easiest way to get the snap-ins (if you do not already have them) is down download a copy of SQL Server Management Studio from Microsoft. If you are running this on a machine with SQL 2008 or later installed, you should be good to go.

So open a Windows PowerShell editor (it doesn’t matter which one, choose your favorite) and load the snap-ins by using the following two commands.

Add-pssnapin SqlServerCmdletSnapin100
Add-pssnapin SqlServerProviderSnapin100

Now we are good to go.

In the following examples, I’ll be querying a localized installation of SQL Server. Change out “localhost\DEFAULT” for your SQL Server. Use DEFAULT if it is not a named instance (that is, server STAGE1 would be STAGE1\DEFAULT). For example, a named instance on a server with the name of STAGE2 and an instance name of Instance1 would be STAGE2\Instance1.

Now let us quickly grab a list of databases on the SQL instance and show their last backup date. The following command accomplishes this task.

dir SQLSERVER:\SQL\localhost\DEFAULT\Databases | Select Name, LastBackupDate | Out-GridView

The output display is shown in the following image.


Image of command output

Hey cool, that was easy, let us go home!

Not so fast.

There is a whole host of information available that can be exposed that we haven’t captured, not to mention that for some reason the Master, Model, and MSDB databases aren’t being shown.

System databases are hidden and we cannot examine their properties without using the Force parameter. So let us try that again with that parameter included:

dir -force SQLSERVER:\SQL\localhost\DEFAULT\Databases | Select Name, LastBackupDate | Out-GridView

Image of command output

Now we see those system databases.

Getting more backup information

So we have the basics. This is a great start; however, we are actually only getting the last time a full backup of the database was performed. If a differential backup had been taken, we would not have known.

Even worse, there is a problem with the results. Run the script and note the retrieved results. Now take a backup of one of your databases and run the script again. You will see that the LastBackupDate value has not changed. This is because the data gets cached. To be sure that we always get the most current information we have to refresh the data.

dir -force SQLSERVER:\SQL\localhost\DEFAULT\Databases | where-object {$_.Name; $_.Refresh()} | Select Name, LastBackupDate, LastDifferentialBackupDate | Out-GridView

Image of command output

Making improvements

So now we have the date for the last time the database was backed up, be it a full or differential backup. This is still not particularly readable though and it shows all the databases on the SQL instance. So how about a couple of improvement ideas:

  • Turn this into an easily executed function that we could put in our profile ready to call.
  • Ignore tempdb because it doesn’t get backed up. (It gets re-created whenever SQL Server is started, so there’s no point.)
  • Tell us whether the last backup was a full or differential as a part of the results.
  • Tell us how long ago the last backup was completed so we don’t have to figure it out for ourselves.
  • Provide us the option to grab information for a single database rather than all of them.
  • Give us a clear warning if a database has never been backed up.

These ideas lead to the following function.

Function Get-DBBackupInfo ($SQLInstance, $DBName)

{

    if ($SQLInstance.Contains("`\"))

        { $location = "SQLSERVER:\SQL\$SQLInstance\Databases" }

    else

        { $location = "SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases" }

  

   $DisplayResults =   @{Label="DB Name";Expression={$_.Name};width=30},

        @{Label="Last Full";Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"NA"}

            ELSE {$_.LastBackupDate.ToString("yyyy/MM/dd HH:mm:ss")}};width=25},

        @{Label="Last Differential";Expression={IF ($_.LastDifferentialBackupDate -eq "01/01/0001 00:00:00") {"NA"}

             ELSE {$_.LastDifferentialBackupDate.ToString("yyyy/MM/dd HH:mm:ss")}};width=25},

        @{Label="Most Recent Backup Type";Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"NA"}

            ELSEIF ($_.LastBackupDate -gt $_.LastDifferentialBackupDate) {"FULL"}

            ELSE {"DIFF"}};width=25},

        @{Label="Days Since Last Backup";Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"Never Backed Up!"}

            ELSEIF ($_.LastDifferentialBackupDate -gt $_.LastBackupDate) {((Get-Date) - $_.LastDifferentialBackupDate).Days}

            ELSE {((Get-Date) - $_.LastBackupDate).Days}};width=25}

       

    if ($DBName)

            {

        dir -force $location | where-object {$_.Name -eq $DBName; $_.Refresh()} |

        format-table $DisplayResults

        }

    else  

            {

        dir -force $location | where-object {$_.Name -ne "tempdb"; $_.Refresh()} |

        format-table  $DisplayResults

        }        

}

Now to return good, relevant information, we simply call this function along with the SQL instance name as shown here.

Get-DBBackupInfo localhost

Image of command and output

The returned results clearly show us when databases were last backed up and the type of backup—and databases that have not been backed up really stand out.

It is still a lot of information to digest if we are only interested in a single database. The way the function was written, we also have the option to provide a database name and only return results for that.

Get-DBBackupInfo localhost adventureworks

Image of command and output

Armed with this, you can very quickly retrieve backup information for a SQL instance and database, so when that user comes a knocking, you can provide quick and accurate results.

Being more proactive

Now that you can quickly get folks out of your hair, let’s take it to the next step. Wouldn’t it be great if you could get that sort of information for all of your SQL Servers, have all that information stored in a single location where you could query it as needed, and send you an email in the event that you have databases that are not being backed up?

This can be done relatively simply thanks to a variation of the previous script by using Invoke-SqlCmd and Send-MailMessage.

Those of you who experienced with SQL Server will say, “I could just query MSDB and get the information I need.”

Yes, you absolutely could; however, MSDB is not going to tell you what databases have not been backed up, and depending on your maintenance and indexing strategies, it could take a significant amount of time to query that data. (For cleansing tips on MSDB data, take a look at Keeping MSDB Clean). Pulling all of that data from MSDB into a central location is certainly viable, but it’s not as quick or as simple to maintain as using Windows PowerShell and the SQL snap-ins.

SQL setup

We are going to use SQL Server to hold all of our data, so we need to create a database and a couple of tables. The Transact SQL (T-SQL) commands to accomplish this are shown here.

CREATE DATABASE SQLInfo

GO

USE SQLInfo

GO

 

 

CREATE TABLE dbo.DatabaseBackups(

      SERVERNAME varchar(50) NULL,

      DatabaseName varchar(128) NULL,

      LastFullBackupDate datetime NULL,

      LastDifferentialBackupDate datetime NULL,

      LastAbsoluteBackupDate datetime NULL,

      LastAbsoluteBackupType char(4) NOT NULL,

      BackupDelta int NOT NULL

) ON [PRIMARY]

 

GO

 

 

CREATE TABLE [dbo].[SQLInstances](

 InstanceName varchar(128)

) ON [PRIMARY]

The SQLInstances table is going to hold a list of our SQL Server instances. We’ll iterate through this list and store backup information for all of the databases on each in the DatabaseBackups table.

To get started, add a single record to the table. This keeps the result set low initially while we ensure that everything is running smoothly.

INSERT INTO SQLInstances VALUES ('localhost')

Now we are ready for a new function:

Function Get-DBBackupToDatabase ($SQLInstance)

{

    #Check whether or not a named instance and set the location accordingly

    if ($SQLInstance -ilike "*\*") {$location = "SQLSERVER:\SQL\$SQLInstance\Databases"}

    else {$location = "SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases"}

   

    

    $DBStats = dir -force $location | where-object {$_.Name -ne "tempdb"; $_.Refresh()}

        foreach ($DB in $DBStats)

        {

        $DBName = $DB.Name

        $LastFull = $DB.LastBackupDate

        IF ($DB.LastDifferentialBackupDate -eq "01/01/0001 00:00:00") {$LastDiff = $NULL} ELSE {$LastDiff = $DB.LastDifferentialBackupDate}

        #The last absolute backup will be the newer of the last full or last differential, we can also set the type using this

        IF ($LastDiff -gt $LastFull) {$LastAbsolute = $LastDiff; $LastType = "DIFF"}

            ELSEIF ($LastFull -eq "01/01/0001 00:00:00"){$LastAbsolute = $LastFull; $LastType = "NONE"}

            ELSE {$LastAbsolute = $LastFull; $LastType = "FULL"}

        #Quick calculation gives us the number of days since the last backup

        $DaysSince = ((Get-Date) - $LastAbsolute).Days

       

        #Because SQL cannot store the default date of 01/01/0001 we set it to null, which will store in sql as 1900-01-01

        IF ($LastFull -eq "01/01/0001 00:00:00") {$LastFull = $NULL}

        IF ($LastAbsolute -eq "01/01/0001 00:00:00") {$LastAbsolute = $NULL}

       

     

$InsertResults = @"

INSERT INTO dbo.DatabaseBackups (ServerName, DatabaseName, LastFullBackupDate, LastDifferentialBackupDate, LastAbsoluteBackupDate, LastAbsoluteBackupType, BackupDelta)

VALUES ('$SQLInstance', '$DBName', '$LastFull', '$LastDiff', '$LastAbsolute', '$LastType', '$DaysSince')

"@      

 

        invoke-sqlcmd @params -Query $InsertResults

        }

   

}

This function will take a SQL instance, grab all the backup information, do a couple of quick calculations on it and then write that information into the dbo.DatabaseBackups table.

Note that the Invoke-SqlCmd here is using @params for the ServerName and Database parameters. This allows us to only specify that information when calling the function and not have to put it in the script multiple times (which really helps when it comes to maintenance).

To call this for every SQL instance listed in the SQLInstances table, we grab a record set and then iterate through, passing each record to the function as follows.

#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside

$params = @{'server'='localhost';

       'Database'='SQLInfo'}

 

#Grab our list of servers, iterate through them and call the function which rights to the database

$Srv = invoke-sqlcmd @params -Query "TRUNCATE TABLE dbo.DatabaseBackups;SELECT InstanceName from SQLInstances"

 

foreach ($Instance in $srv)

{

    Get-DBBackupToDatabase $Instance.InstanceName

}

All our backup information will now be loaded into the DatabaseBackups table. We can run a quick SQL query to check this.

SELECT * FROM dbo.DatabaseBackups

Image of backup table

If you have 10, 20, or 100 SQL instances, being able to quickly load all backup information into a single table and query it to find that information is immensely useful. To make it more useful though, we should send that email notification.

Emailing backup information

Once more, Invoke-SqlCmd comes to the rescue. We grab the information that we want from SQL, pretty it up a little by converting the results to HTML, apply some style sheet information, and then send the email (you need to change the requisite to/from/smtp information so that the email will send).

#Stylesheet info used for returning good looking results

$style = '<style>'

$style = $style + 'BODY{background-color:peachpuff;}'

$style = $style + 'TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}'

$style = $style + 'TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}'

$style = $style+ 'TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}'

$style = $style + '</style>'

 

#So we can get some information back we pull a list of databases not backed up in the previous day

#And send them our email recipient

$FailedBackups = invoke-sqlcmd @params -Query "Select ServerName,

    DatabaseName

    , case LastAbsoluteBackupDate

            when '1900-01-01 00:00:00' then 'Never Backed Up'

            else convert(varchar(20), LastAbsoluteBackupDate, 120)

            end as LastBackup

    , case LastAbsoluteBackupDate

            when '1900-01-01 00:00:00' then 'Never Backed Up'

            else convert(varchar,BackupDelta)

            end as BackupDelta

    from dbo.DatabaseBackups where BackupDelta > 0" | convertTo-Html -Head $style -Property ServerName, DatabaseName, LastBackup, BackupDelta | out-string;

 

#Don't forget to set your smtpserver information

Send-MailMessage -To "me@me.com" -Subject "Databases Not Backed Up" –From "me@me.com" -SmtpServer "mysmtpserver" -Body $FailedBackups –BodyAsHtml

Your email should look something like this (assuming you have databases that have not been backed up in the last day).

Image of email

Putting it together

We now have a script that will do everything for us: grab the data, store it, and email us when there are backup issues.

Function Get-DBBackupToDatabase ($SQLInstance)

{

    #Check whether or not a named instance and set the location accordingly

    if ($SQLInstance -ilike "*\*") {$location = "SQLSERVER:\SQL\$SQLInstance\Databases"}

    else {$location = "SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases"}

   

    

    $DBStats = dir -force $location | where-object {$_.Name -ne "tempdb"; $_.Refresh()}

        foreach ($DB in $DBStats)

        {

        $DBName = $DB.Name

        $LastFull = $DB.LastBackupDate

        IF ($DB.LastDifferentialBackupDate -eq "01/01/0001 00:00:00") {$LastDiff = $NULL} ELSE {$LastDiff = $DB.LastDifferentialBackupDate}

        #The last absolute backup will be the newer of the last full or last differential, we can also set the type using this

        IF ($LastDiff -gt $LastFull) {$LastAbsolute = $LastDiff; $LastType = "DIFF"}

            ELSEIF ($LastFull -eq "01/01/0001 00:00:00"){$LastAbsolute = $LastFull; $LastType = "NONE"}

            ELSE {$LastAbsolute = $LastFull; $LastType = "FULL"}

        #Quick calculation gives us the number of days since the last backup

        $DaysSince = ((Get-Date) - $LastAbsolute).Days

       

        #Because SQL cannot store the default date of 01/01/0001 we set it to null, which will store in sql as 1900-01-01

        IF ($LastFull -eq "01/01/0001 00:00:00") {$LastFull = $NULL}

        IF ($LastAbsolute -eq "01/01/0001 00:00:00") {$LastAbsolute = $NULL}

       

     

$InsertResults = @"

INSERT INTO dbo.DatabaseBackups (ServerName, DatabaseName, LastFullBackupDate, LastDifferentialBackupDate, LastAbsoluteBackupDate, LastAbsoluteBackupType, BackupDelta)

VALUES ('$SQLInstance', '$DBName', '$LastFull', '$LastDiff', '$LastAbsolute', '$LastType', '$DaysSince')

"@      

 

        invoke-sqlcmd @params -Query $InsertResults

        }

   

}

 

#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside

$params = @{'server'='localhost';

       'Database'='SQLInfo'}

 

#Grab our list of servers, iterate through them and call the function which rights to the database

$Srv = invoke-sqlcmd @params -Query "TRUNCATE TABLE dbo.DatabaseBackups;SELECT InstanceName from SQLInstances"

 

foreach ($Instance in $srv)

{

    Get-DBBackupToDatabase $Instance.InstanceName

}

 

#Stylesheet info used for returning good looking results

$style = '<style>'

$style = $style + 'BODY{background-color:peachpuff;}'

$style = $style + 'TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}'

$style = $style + 'TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}'

$style = $style+ 'TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}'

$style = $style + '</style>'

 

#So we can get some information back we pull a list of databases not backed up in the previous day

#And send them our email recipient

$FailedBackups = invoke-sqlcmd @params -Query "Select ServerName,

    DatabaseName

    , case LastAbsoluteBackupDate

            when '1900-01-01 00:00:00' then 'Never Backed Up'

            else convert(varchar(20), LastAbsoluteBackupDate, 120)

            end as LastBackup

    , case LastAbsoluteBackupDate

            when '1900-01-01 00:00:00' then 'Never Backed Up'

            else convert(varchar,BackupDelta)

            end as BackupDelta

    from dbo.DatabaseBackups where BackupDelta > 0" | convertTo-Html -Head $style -Property ServerName, DatabaseName, LastBackup, BackupDelta | out-string;

 

#Don't forget to set your smtpserver information

Send-MailMessage -To "me@me.com" -Subject "Databases Not Backed Up" –From "me@me.com" -SmtpServer "mysmtpserver" -Body $FailedBackups -BodyAsHtml

You can save this script and execute it manually whenever you need to. However, to get the most out of it, I would recommend scheduling it daily by using SQL Server Agent. Doing this will give you early notification of issues and allow you to act quickly to get those databases backed up.

CN, that is all there is to using Windows PowerShell to query for SQL Server database backups. SQL week will continue tomorrow when we will welcome another guest blogger.

Thank you, Nicholas, for writing this blog and sharing your experience with our readers.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Great!

    If you can convince Oracle to bring these functions as Cmdlets to me ... this will be amazing!

    We have some SQL servers too, but the main data storage is hosted by Oracle DBs.

    kind regards, Klaus

  • It’s a great script, but I’m having an issue with it: I get backup dates when I know the database has not been backed up. It seems that when the system backup runs (the server is a VM on VMWare backed up with Backup Exec 2010), the script is returning that as the “Full backup” time. Is there a way to work around that? I really only want the time of the SQL backup.

    Thanks, Chris

  • Great article Ed, many thanks for taking the time.

    Had some difficulty myself getting the snappins to load, so went a different route to connect to the db, pulled from this blog:

    www.mssqltips.com/.../check-the-last-sql-server-backup-date-using-windows-powershell

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     $sql = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "$servername"

     $sql.Databases | format-table $DisplayResults

    Is the guts of it, just in case anyone else has similar issues.

  • Works great! I don't get though how to run as a sql job?

    it's a ps1 file

  • getting below error:

    The term 'Get-DBBackupInfo' is not recognized as the name of a cmdlet, function
    , script file, or operable program. Check the spelling of the name, or if a pat
    h was included, verify that the path is correct and try again.
    At line:1 char:17
    + Get-DBBackupInfo <<<< localhost
    + CategoryInfo : ObjectNotFound: (Get-DBBackupInfo:String) [], Co
    mmandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException