Hi,
This code helps me keep track of what's being backed up, and more importantly, what is not.
You'll need a DB and table to store the data in.
ALTER PROCEDURE [dbo].[usp_GetSvrDBBackups] @svr Varchar(50), @DB Varchar(50), @TopN TinyInt = 1
AS
SET NOCOUNT ON
--DECLARE @svr Varchar(50), @DB Varchar(50), @TopN TinyInt
--SET @svr = 'Server1\Instance2'
--SET @DB = 'DBADB'
--SET @TopN = 5
DECLARE @SQL Varchar(MAX)
-- Get rid of the old data.
DELETE FROM AllBackups WHERE SvrName = @svr AND DBName = @DB
/*
Backup Types:
L = Log
D = Full
I = Differential
*/
SET @SQL = '
INSERT INTO AllBackups (SvrName, DBName, BackupSize, BackupStart, BackupFinish, BackupType, DeviceName, Server_Name)
SELECT TOP ' + CAST(@TopN AS Varchar) + ' ''' + @svr + ''', BS.Database_name, BS.backup_size, BS.backup_Start_date, backup_finish_date,
BS.type, BMF.Physical_Device_Name, BS.Server_Name
FROM [' + @svr + '].MSDB.DBO.BackupSet BS
INNER JOIN [' + @svr + '].MSDB.DBO.BackupMediaFamily BMF ON BS.Media_Set_ID = BMF.Media_Set_ID
WHERE Type = ''D''
AND Database_name = ''' + @DB + '''
ORDER BY backup_finish_date DESC
INSERT INTO AllBackups (SvrName, DBName, BackupSize, BackupStart, BackupFinish, BackupType, DeviceName, Server_Name)
SELECT TOP ' + CAST(@TopN AS Varchar) + ' ''' + @svr + ''', BS.Database_name, BS.backup_size, BS.backup_Start_date, backup_finish_date,
BS.type, BMF.Physical_Device_Name, BS.Server_Name
FROM [' + @svr + '].MSDB.DBO.BackupSet BS
INNER JOIN [' + @svr + '].MSDB.DBO.BackupMediaFamily BMF ON BS.Media_Set_ID = BMF.Media_Set_ID
WHERE Type = ''L''
AND Database_name = ''' + @DB + '''
ORDER BY backup_finish_date DESC
INSERT INTO AllBackups (SvrName, DBName, BackupSize, BackupStart, BackupFinish, BackupType, DeviceName, Server_Name)
SELECT TOP ' + CAST(@TopN AS Varchar) + ' ''' + @svr + ''', BS.Database_name, BS.backup_size, BS.backup_Start_date, backup_finish_date,
BS.type, BMF.Physical_Device_Name, BS.Server_Name
FROM [' + @svr + '].MSDB.DBO.BackupSet BS
INNER JOIN [' + @svr + '].MSDB.DBO.BackupMediaFamily BMF ON BS.Media_Set_ID = BMF.Media_Set_ID
WHERE Type = ''I''
AND Database_name = ''' + @DB + '''
ORDER BY backup_finish_date DESC'
--PRINT @SQL
EXEC (@SQL)
--SELECT * FROM AllBackups
EXEC usp_Upt_Refreshed @svr = @svr, @DB = @DB, @Cat = 'Backups'
You can then cycle through your servers easily with : (I have a table with all database and server names. (AllDBs)
ALTER PROCEDURE [dbo].[usp_GetAllServerBackups]
AS
SET NOCOUNT ON
DECLARE @svr Varchar(50), @DB Varchar(50)
--SET @svr = 'Server\Instance'
--SET @DB = 'TestDB'
DECLARE cSvrs Cursor FOR SELECT DISTINCT SvrName, DBName FROM AllDBs
OPEN cSvrs
FETCH NEXT FROM cSvrs INTO @svr, @DB
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC usp_GetSvrDBBackups @svr = @svr, @DB= @DB, @TopN = 1
FETCH NEXT FROM cSvrs INTO @svr, @DB
END
CLOSE cSvrs
DEALLOCATE cSvrs
Using a central table with all database and servers allows you to create code to pull out and store anything you need. E.g. Tables, Columns, Jobs, Triggers, SPs etc...
Your central server will need linked servers to each of your servers you want monitor.
( SELECT TOP 1 * FROM sys.Servers. make sure is_Linked = 1 otherwise you will encounter irritating problems.)
Hope this points you in the right direction.
Goodluck.