Does It Measure Up?
DB snapshots are advertised to solve two main problems
- Use as a reporting server. We can use DB snapshots as a reporting database, but not as a reporting "server." Remember that DB snapshots can only be created on the same server as the main database. So if you use it as a reporting server, it will share the processing power of the same server, and therefore will not improve the performance of the source database. There is an exception, however; you could set up a DB snapshot on a SQL Server 2005 mirror server and hence use its processing power. But since the mirror server feature is not included in SQL Server 2005, it’s only a future hope.
- Guarding against user errors. As mentioned earlier, DB
snapshots can be used to prevent user errors. But you need to be aware that
this isn’t a foolproof solution. For example, consider this scenario:
An automated script creates a DB snapshot.
User A inserts rows into the database.
User B deletes the rows that were inserted by User A. User B then realizes it was an error and requests that the database administrator recover the rows that were accidentally deleted.
Because the snapshot was created at 10:00, it doesn’t have any information on the rows inserted at 10:01 by User A. Hence, those rows cannot be recovered using the DB snapshot. To minimize such scenarios, you could create snapshots at frequent intervals. But no matter what, gaps will exist.