Wednesday, November 23, 2005

House Keeping Part II

Is your entire database backed up?
I am sure most of you agree with me but don’t be surprised to find out that some of your database never backed up. I have come across this situation many times and have decided to put a simple script to tell me the backup status of all databases in one instance of SQL Server.

select cast(@@servername as varchar(15)) Server_Name,
cast(d.name as varchar(20)) Database_Name,
substring(suser_sname(d.sid), 1, 17) Database_Creator,
'Never Backed Up' Last_Backup_Date
from master..sysdatabases d
where d.name not in (select distinct database_name from msdb..backupset where server_name=@@servername)
and d.name not in ('tempdb')
union all
select cast(server_name as varchar(15)),
cast(database_name as varchar(20)),
substring(suser_sname(d.sid), 1, 17),
cast(max(backup_finish_date) as varchar(20))
from msdb..backupset b join master..sysdatabases d on b.database_name=d.name
where server_name=@@servername
group by Server_Name, Database_Name, d.sid



Please feel free to send me your comments.

No comments: