Get Databases with no backup

Question

Post reply

Get Databases with no backup

BruceHJames

Valued Member

Points: 65
More actions
October 24, 2012 at 2:54 am

#263776

Comments posted to this topic are about the item Get Databases with no backup

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jim Blackson SSC Enthusiast Points: 125 More actions · Answer 1

For MS-SQL 2000, the list of databases is only in the master database.

http://msdn.microsoft.com/en-us/library/aa260406%28v=sql.80%29.aspx

Modifying "sys.databases" to read "master..sysdatabases" worked for me. Here is the resulting script:

use msdb

go

select distinct

d.name

from master..sysdatabases d

left join backupset bs

on bs.database_name = d.name

where bs.database_name is null

and d.name <> 'tempdb'

order by d.name

Best regards,
jimb.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 2

It's a simple and effective tool!

I would change it to include any databases whose last backup is older than X days.

It's also intesresting to see how restoring a database from server A to server B creates a row in the msdb.backupset table of server B indicating where the backup came from and when it was done, etc.