Data loss easier than recovery

"This week, Microsoft announced that they had lost all Sidekick user data including pictures, contacts, calendars and other information from the Danger's servers. Since the devices sync with the servers, the devices also lost the data. The Sidekick data services had amazingly been out over a week.

From what they say, after a week of investigation, there is no way to retrieve the user data. Customers will have to start over. "

Link to story

And the moral of the story is... (for the MS cloud providers anyway)


Find Last BackUp Date Of All Databases on your Server

Whenever you perform a backup, SQL Server 2005 updates the following tables: msdb.dbo.backupfile, msdb.dbo.backupmediafamily, msdb.dbo.backupmediaset and msdb.dbo.backupset.

You can use these tables to retrieve backup information about your database. In the following query, a join is made between sys.sysdatabase and msdb.dbo.backupset to fetch the database name, last backup date and the user who took the backup.

SQL Server 2005 Solution
SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

You can run this on multiple servers using SQL 2008 and the new server group feature.

You can exclude servers that aren't required to be monitored in the group by placing
AND @@servername not in ('servername')
in the where clause.

This is my take on the query:

Use msdb
go
select name,type,database_name,last_backup
from

(SELECT sd.name,
bs.TYPE,
bs.database_name,
max(bs.backup_start_date) as last_backup
FROM sys.databases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
where bs.[type] not in ('I')
and (sd.recovery_model <> 3 or bs.type not in ('L'))
and sd.is_read_only = 0
and @@servername not in ('localhost')
Group by sd.name,
bs.TYPE,
bs.database_name

) sd
where sd.last_backup <= dateadd(d,-1,getdate())
Order by sd.name,last_backup

All the databases that haven't had full backups in the last day.

For Sidekick users, the moral of the story is... backup your data outside of the cloud.

Should be an interesting week for those using Sidekicks to their full potential, and a blow to cloud computing proponents.

What would happen of any of these vendors announced they had lost all your (their) data?

Facebook
Myspace
Google Apps
Amazon S3
Blogger
Youtube
iTunes
Kindle

Something to think about...

Cloud-computing and the seven deadly data risks