Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

SQL Server backup review with Powershell

21 July,2009 by Tom Collins

The BACKUP team requested a report outlining the last backup of every database on every SQL Server instance, covering : full , incremental and log type backups. The idea was to compare this against their schedule , checking for differences .

The SQL Server inventory in the organisation is 750 SQL Server instances , using SQL server 2000 , 2005 and SQL Server 2008. The solution had to involve iterating through a list of sql server instances ,  executing some t-sql  code, adding the results to an output file. Following is the steps , with relevant details


Create a .txt file with a list of all relevant instances , such as



Create a .sql file with the following code , and save as backups_last_good_backups.sql. In this case I wanted to raise an ALERT , if a backup hadn't occured in 7 days , or if no backup existed at all.:

* SQL Server - Last known good backup 
* Run this script against a sql server instance to check when last known
* good backups were taken
* The script displays : name of db , backup type , backup set db name , last backup
SELECT  (SELECT   RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as instance, as name,
        max(bs.backup_start_date) as last_backup,
        note = CASE 
   WHEN max(bs.backup_start_date) < GETDATE() - 7 THEN 'ALERT'
   WHEN ISNULL(max(bs.backup_start_date),0) = 0 THEN 'ALERT'
   ELSE '---'
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(
        left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
        WHERE NOT LIKE '%tempdb%'
Group by,
Order by,last_backup


Create a powershell file , and place in the following code . Note : on line 1 is the location of the file you created in Step 1 Line 2 , invokes the SQLCMD (which needs to be installed on whichever computer you are execuring this powershell code.) It accepts the the $svr variable , and runs the .sql code , saved in step 2. This outputs the results into "backupAudit_SQL_Server_output.txt" in an iterative mode.


 foreach ($svr in get-content "C:\instances_backup.txt"){
        invoke-expression "SQLCMD -E -S $svr -i backups_last_good_backups.sql -v inst_name=$svr -Y30 >> backupAudit_SQL_Server_output.txt"


Ensuring your powershell code and SQL code are in the same directory ,Run the powershell code in your Powershell command line - and then check the output , it should be something like :

instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER1_INST1               master                         D    master                         2009-06-27 23:59:53.000 --- 
MYSERVER1_INST1               model                          D    model                          2009-06-28 00:03:01.000 --- 
MYSERVER1_INST1               msdb                           D    msdb                           2009-06-28 00:03:07.000 --- 
MYSERVER1_INST1               Rightdbs                       I    Rightdbs                       2009-06-26 20:30:26.000 --- 
MYSERVER1_INST1               Rightdbs                       D    Rightdbs                       2009-06-28 00:03:18.000 --- 

(5 rows affected)
instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER2_INST2             master                         NULL NULL                                              NULL ALERT
MYSERVER2_INST2             model                          NULL NULL                                              NULL ALERT
MYSERVER2_INST2             msdb                           NULL NULL                                              NULL ALERT
MYSERVER2_INST2             db3nn                          D    db3nn                          2009-06-18 19:56:10.000 ALERT

Read More

Send email from Powershell with attachment - SQL Server DBA

Powershell , Excel charts and data presentation - SQL Server

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server backup review with Powershell | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer