Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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:
Time
Activity
10:00 AM
An automated script creates a DB snapshot.
10:01 AM
User A inserts rows into the database.
10:02 AM
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.