I had to produce a report to the Backup team outlining the last backup of every database on every instance.
This had to cover : full , incremental and log type backups. The idea was to compare this against their schedule , checking for disparities .
The sql server inventory in the organisation is 750 sql server instances , using SQL server 2000 and 2005. The solution had to involve iterating through a list of sql server instances , and 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
Comments