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
MYSERVER1INSTANCE1
MYSERVER2INSTANCE2
MYSERVER3INSTANC3
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
Author: Jack Vamvas
Leave a Reply