So you can recover from database snapshots...but...

SQL Server 2005 has given us the option to create database snapshots to create a point-in-time image of the database. This gives us the option to restore from a point-in-time in case of user errors, like maybe accidentally truncating a table. MSDN has provided us a procedural approach on how to restore from database snapshots. But what I am more concerned the most is that I have already lost a lot of data from the time the snapshot was created to the point in time that the error occurred. And this is where your understanding of what happened and how you implement your disaster recovery plan come into the picture. As far as Microsoft is concerned, restoring a database from a snapshot overwrites the original source database. But here's what I'd do. I'd first backup the tail of the log before I restore from the database snapshot. After restoring from the snapshot, I'd restore my backups into another copy of the database and restore the tail of the log. This will be the state of the database after the accidental error occurred. Since I am concerned about the new records that were added after the accidental error, I would retrieve those from my database copy and isert those on my restored database. This will take a lot of work as you will be comparing the tables from the original and the copy database. At least you managed to restore the records faster than restoring from a FULL backup. There is no native way to solve this but there are a lot of third-party utilities that can just rollback a specific command from the transaction log. You can use the TableDiff utility to do this although it wasn't meant to solve this type of problems

Did you check your MSDB.dbo.suspect_pages after a database restore?

In SQL Server 2005, the default behavior of a RESTORE command is to simply continue even if there are corrupted pages in your backups. The only way to find out if there are corruptions is when a user gives you a call saying that they could not query some records and probably gets an error similar to the one below

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0xf93a020c; actual: 0xf93a820c).
It occurred during a read of page (1:69) in database ID 10 at offset 0x0000000013c000 in file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SalesDB.mdf'.
Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.

The first thing you need to do after a database restore is to check the MSDB.dbo.suspect_pages to see if there are suspect pages that need to be repaired. Books Online describes the structure of this system table and gives you an idea on what to restore should there be suspect pages. Bear in mind though that this system table can only hold up to a maximum of 1000 records and everything get cycled out so you need to do your own housekeeping.

The Enterprise Edition gives you an option to do a page-level restore should you have corrupt databases. A more low-level discussion on fixing damaged pages can be found on the SQL Server Storage Engine blog

You don't trust your database backups? Use mirrored backup media sets in SQL Server 2005

What could be more frustrating than knowing that your database backups went missing? This is specifically true if you are dealing with transaction log backups which are dependent on log sequence numbers. You don't want to lose a single transaction log backup in the chain. In previous versions of SQL Server, we just execute a copy command (or even ROBOCOPY) to copy the transaction log backups to a different location. In SQL Server 2005, we have the MIRROR TO clause. This specifies a set of one or more backup devices that will mirror the backups devices specified in the TO clause, which could be a tape, disk or network location.

To use the MIRROR TO clause, see the sample script below

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksDB2.BAK'
MIRROR to DISK = 'F:\AdventureWorksDB.BAK'
WITH INIT, FORMAT, STATS=10
GO

This creates a mirrored copy of the database backup of the AdventureWorks database to the F:\ drive. Although this creates a mirrored copy of the backup, it will definitely take quite some time to complete as it is writing on all the mirrored media set, thereby, increasing database your backup window. I did a test on this by using the MIRROR TO DISK clause with the AdventureWorks database and it takes like 10 times longer to do a backup with this option - the speed, of course, would be dependent on where you mirror your backups and its IO performance

I'd probably still stick to using the copy command in my backups as long as I get the same result. For more information on using mirrored backup media sets, check out this MSDN article

You need to backup the mssqlsystemresource database files

So, you think you have all the necessary database backups you need? Well, think again. This might be your mindset after moving from SQL Server 2000 to SQL Server 2005. SQL Server 2005 includes a read-only, hidden database that contains all the system objects. It comes in the form of mssqlsystemresource.mdf and mssqlsystemresource.ldf files located in :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ by default. This database is required by the master database and will definitely affect how your SQL Server service behaves during restarts. If you move the master database, you should also move these files no the same location. Since it is a hidden database, SQL Server won't be able to backup this database as part of your database maintenance plan. One approach you could use is to xcopy it to the location of your database backups on a regular basis.

MSDN has a documentation on what this is all about but let me tell you one thing - you MUSt include this in your backup procedures. If you want to have an idea of how critical these files are, stop your SQL Server 2005 service and rename the mssqlsystemresource.mdf file. You won't be able to restart your SQL Server service afterwards