SQL Server backup review with Powershell

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

STEP 1

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

MYSERVER1\INSTANCE1
MYSERVER2\INSTANCE2
MYSERVER3\INSTANC3

STEP 2

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
*A
******************************************************************/
SELECT  (SELECT   RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as instance,
        sd.name as name,
        bs.type,
        bs.database_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 '---'
   END
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
        left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
        WHERE sd.name NOT LIKE '%tempdb%'
Group by sd.name,
        bs.type,
        bs.database_name
Order by sd.name,last_backup

STEP 3

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"
 }  

STEP 4

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