We previously ran Crystal Reports straight off our production database. This caused a direct impact to the users using the database. We moved to a new (dedicated) server and a new database and ditched Crystal Reports in favor of SSRS. We also have a dedicated reporting server to which our production database is copied over to twice a day. We run queries off of the reporting server as to not slow down the production environment.

Is this the best solution to our problem? The backup restorations kick everyone off the reporting server whenever they occur and management is asking we step up this process (backup restoration) to once every two hours. However, just twice a day gives us issues. For example, as I write this, the database is stuck in "Restoring..." and has been for at least 12 hours. So in the meantime that the DBAs come in (I only write reports) I thought I'd ask. Is this the best possible setup we've got going on?

4 Answers 4


This is what Replication is for. This is a much easier way to keep a reporting database in sync than backup/restore.

  • We talked to the DBA and he confirmed this was the perfect solution for us. Not sure why he was a couple of days from setting us up with a log shipping restore every two hours. Would have made the DB almost inaccessible.
    – Langosta
    Mar 27, 2013 at 17:13

If the database copy can be readonly then try to use: database snapshots. It creates a readonly copy of the current database state in a small amount of time.

More info here: http://msdn.microsoft.com/en-us/library/ms175158(v=sql.100).aspx

Schedule this code into a job that runs once or multiple times a day:



Rather than burden the production system with the overhead (and potential changes required) to support replication or other techniques, if it is still acceptable to have "stale" data for a couple of hours, why not use two restored copies for reporting?

  • At 6 AM, take a COPY_ONLY backup of production.
  • Restore it on the reporting server as Report_A.
  • When the restore is finished, users connect to Report_A.
  • At 8 AM, take a COPY_ONLY backup of production.
  • Restore it on the reporting server as Report_B.
  • When the restore is finished, users connect to Report_B.
  • Rinse, repeat.

You could update some table after every restore, and have your application be smart enough to generate a connection string based on what's newest.

There still may be times where a user is connected to the "old" when the restore takes place, but this should be a much less frequent problem, since the new copies are restored in the background while they continue to work, instead of replacing what they're working on.


This is one of the exact use cases that Microsoft created always on availability groups for. Queries with read intent (like your reporting queries) can be automatically routed to the secondary replica.

The downside is this requires upgrading to SQL server 2012 and requires enterprise edition. I don't know if that is a possibility for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.