{"id":14061,"date":"2011-05-02T00:01:00","date_gmt":"2011-05-02T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/05\/02\/use-powershell-to-report-sql-server-backup-status\/"},"modified":"2011-05-02T00:01:00","modified_gmt":"2011-05-02T00:01:00","slug":"use-powershell-to-report-sql-server-backup-status","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-report-sql-server-backup-status\/","title":{"rendered":"Use PowerShell to Report SQL Server Backup Status"},"content":{"rendered":"

Summary<\/b>: Learn how to use Windows PowerShell to automatically email a report on SQL Server backup status.<\/p>\n

\"Hey, Hey, Scripting Guy! <\/p>\n

—CN<\/p>\n

\"Hey, Hello CN, Microsoft Scripting Guy, Ed Wilson, here. The time is near for SQLRally<\/a> 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. <\/p>\n

\n

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<\/a> 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<\/a>. <\/p>\n<\/blockquote>\n

Checking SQL Server backups<\/h3>\n

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.<\/p>\n

\"Photo<\/a><\/p>\n

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. <\/p>\n

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. <\/p>\n

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<\/a> on MSDN for more details).<\/p>\n

So how can I check my backups?<\/h3>\n

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.<\/p>\n

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<\/a> from Microsoft. If you are running this on a machine with SQL 2008 or later installed, you should be good to go.<\/p>\n

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.<\/p>\n

\n

Add-pssnapin SqlServerCmdletSnapin100
Add-pssnapin SqlServerProviderSnapin100<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

Now we are good to go. <\/p>\n

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.<\/p>\n

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.<\/p>\n

\n

dir SQLSERVER:\\SQL\\localhost\\DEFAULT\\Databases | Select Name, LastBackupDate | Out-GridView<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

The output display is shown in the following image.<\/p>\n

\"Image<\/a><\/p>\n

Hey cool, that was easy, let us go home!<\/p>\n

Not so fast. <\/p>\n

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.<\/p>\n

System databases are hidden and we cannot examine their properties without using the Force<\/i> parameter. So let us try that again with that parameter included:<\/p>\n

\n

dir -force SQLSERVER:\\SQL\\localhost\\DEFAULT\\Databases | Select Name, LastBackupDate | Out-GridView<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

\"Image<\/a><\/p>\n

Now we see those system databases. <\/p>\n

Getting more backup information<\/h3>\n

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.<\/p>\n

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.<\/p>\n

\n

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

\"Image<\/a><\/p>\n

Making improvements<\/h3>\n

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:<\/p>\n