Monday, May 10, 2010

Check that all databases on all servers have been backed up

At the client that I work at, there are over 1500 databases on more than 200 SQL servers, ranging from SQL 2000 to 2008. For me to have a good nights sleep, I want to know that each and every database is being and has been backed up. I could check each databases properties, I could go through 200 emails, and other such tedious methods?! Heck no, read the title of this Blog!!!

Prior to SQL 2008 and the Powershell provider it ships with (sqlps.exe) you could either write your own cmdlets or using SQLPSX from Codeplex, thanks Chad. The beauty with Powershell and SMO and .Net is that a database is an object and the lastbackupdate is a property of that object. Grab the databases collection of a SQL server and for each database get the last backup date. Using T-SQL you'd join a few system tables and write some code. Thats for 1 server, for multiple servers you could use any one of a number of methods to get this information. All this is quite a bit more tedious than the PS approach.

The high level PS approach is I have a list of SQL servers, for each server go to the databases collection and return the database name and the last backup date. That's as simple as it gets and the code is not much more complicated. Well sort of.

To automate the above I can use the SQL Agent on my DBA Management server that has SQL 2008 client tools installed. I can create a SQL Agent Job that runs a Powershell step. How cool is that?!! So in the command window I type vanilla PS code. Whoop!! Thing is, and this is what to me a while to figure out, the PS shell that SQL agent runs is the vanilla shell, not the sqlps shell with the SQL snapins. So to get around this I created a profile file that applies to the native powershell shell that is used by all users.

I create the file and add the two entries using powershell:

ni C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -type "file"
ac -path C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -value "Add-PSSnapin SqlServerCmdletSnapin100"
ac -path C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -value "Add-PSSnapin SqlServerProviderSnapin100"

The great thing now is that, besides from SQL Agent, I can browse SQL like a directory. What I do from the SQL Agent Job is just that. The code:

$svl = 'SQLsvr1', 'SQL-Dev01', 'SQL2k-03'
foreach ($s in $svl) {gci sqlserver:sql\$s\default\databases | select parent, name, lastbackupdate| where {$_.lastbackupdate -lt $(Get-Date).AddDays(-7)}}

Thats it! The above queries my server list ($svl) and gets the properties (gci or get-childitme) of each database in the databases collection for each server. The specific property is the LastBackupDate and where its older than 7 days ago.

Work smart, sleep easy. ;-)

No comments:

Post a Comment