Wednesday, October 21, 2009

Check Last Backup

Recently, T-Mobile and Danger, the microsoft-owned subsidiary that makes the SideKick, has just announced that they've likely lost all user data that was being stored on Microsoft's Servers due to a server failure.
Source : T-MObile Sidekick Disaster: Danger's Servers Crashed, And They Don't Have A Backup

Database Server with important data without backup will kill your company. How you know you already backup your entire important database?

I not remember where I get this script, but basically this script will tell us when is our last full backup of sql database

SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as
LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
WHERE T2.TYPE='D'
GROUP BY T1.Name
ORDER BY T1.Name

if you want know more then full backup, you can run the below script

SELECT
T1.Name as DatabaseName,
backuptype=case type
when 'D' then 'Database'
when 'I' then 'Differential database'
when 'L' then 'Log'
when 'F' then 'File or filegroup'
when 'G' then 'Differential file'
when 'P' then 'Partial'
when 'Q' then 'Differential partial'
else 'NA'
end,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as
LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name,T2.Type
ORDER BY T1.Name

reference :
BackupSet
T-MObile Sidekick Disaster: Danger's Servers Crashed, And They Don't Have A Backup

No comments: