July 16, 2012 at 12:47 am
Good Day,
we have a large number of databases on different servers that have to be backed up . I know there is a standard backup report for SQL Server 2005 and SQL Server 2008, but that is for individual databases . But is there a report we can generate of all the databases that have been backed up on ONE server ? Any ideas ?
July 16, 2012 at 2:18 am
The system tables in MSDB will help.
You will want backupset and the ones it links to, to generate the full picture
July 16, 2012 at 2:36 am
Thank you for the feedback. However the auditors want every database on a specific server being verified that it was backed up. We have a large environment and cannot check severy backup. Is there a report we can generate ?
July 16, 2012 at 2:40 am
Yes if you write it manually.
MSDB.DBO.BACKUPSET, will tell you what was backed up, when it was backed up, who backed it up etc etc.
If you have a large environment I would seriously look at a SQL 2008 server as a central management server where you can issue 1 query against all servers in 1 go, and bang you got your report
July 16, 2012 at 10:21 am
Here is a SQL Prompt snippet I use regularly:
SELECT sdb.name AS DatabaseName,
bus.recovery_model AS RecoveryModel,
/*
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
*/
bus.type AS BackupType,
COALESCE(CONVERT(VARCHAR(50), MAX(bus.backup_finish_date), 121), '-') AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
AND sdb.recovery_model_desc = bus.recovery_model COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE bus.database_name != 'tempdb'
GROUP BY sdb.name,
bus.recovery_model,
bus.type
ORDER BY DatabaseName,
RecoveryModel,
BackupType;
Install a copy of SSMS 2008 or above. Open a multi-server query window in SSMS that connects to all the SQL 2005 or above instances you are interested in reporting from. Run the above query in the multi-server query window. Copy and paste the grid results into Excel. Done.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply