database backup report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

database backup report

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-21 : 09:40:05

Dana writes "Is there a way that I could have a report that verifies that all of my sql databases on the network have been backed-up? Currently I go to the drive that they are backup-up to and open each folder to see if the backup occurred. Any tool would be helpful. Thank you."

schuhtl
Posting Yak Master

102 Posts

Posted - 2006-07-21 : 22:42:23

Have you looked at Multi Server Administration? I use it to manage all of my database maintenance jobs (backup, redindex, checkdb, etc....). It is very helpful to have all of the job history in a central location and it is very easy to setup.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_automate_7ir2.asp

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-07-22 : 15:16:58

That looks interesting. If the master server fails, or a network connection breaks, does that prevent jobs from running on client servers? Or do the client servers keep their own copies of the jobs from the master server?

schuhtl
Posting Yak Master

102 Posts

Posted - 2006-07-24 : 09:56:10

The target/client servers keep their own copies however they can only be modified at the master server.

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 10:53:13

"Is there a way that I could have a report that verifies that all of my sql databases on the network have been backed-up?"

I don't think this has much fail-safe if a backup fails (I haven't checked if the entry is STILL made even if you get a disk full, for example), but this will list the 100 most recent backups - you could modify it for "Databases which have not been backed up today" or some-such, or join it to [sysdatabases] to list databases NOT backed up in X days etc.


SELECT TOP 100
	database_name,
	backup_set_id,
	backup_start_date,
	backup_finish_date,
	type,
	name,
	user_name,
	first_lsn,
	last_lsn,
	database_backup_lsn
	, * 
FROM	msdb.dbo.backupset
--WHERE	    database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
-- 	AND type='D'				-- L=Transaction, I=Differential, D=Full
ORDER BY backup_start_date DESC

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources