Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account