asked
Speed up nightly restore of reporting database
Every night we backup / restore our production database to a reporting server, which is used to query activity from the previous day / overnight hours, among various other reporting. We want to minimize the amount of time that the process takes, while making the database as current as possible. Right now we are backing up the full database weekly, with daily differential backups (along with frequent transaction log backups). When the daily differential backup completes, it launches a restore on the report server (the backup file is saved to the report server). The restore takes about 2.5 hours to complete since it has to restore the full backup every night, then the differential - the database is approaching 1 terabyte in size. I'm thinking that I could speed the whole thing up by starting the restore from the full backup much earlier, then leaving the report database in recovery mode until a differential backup of production is taken, then finishing the restore with that differential backup as soon as it's done. The differential backups are pretty small, 3-4 GB the first night, approaching 10 GB by the end of the week. It's fine if the report server is unavailable for the overnight hours. For example, Saturday, 10:00 PM: Take weekly full backup (about 2 hours) Daily, 2:00 AM: Start restore of latest full backup on report server with NORECOVERY (about 2 hours). Daily, 6:00 AM: Start differential backup (about 10 minutes), then automatically restore this differential to report server with RECOVERY (less then 30 minutes I think). The report database is supposed to be up by 7:00 AM. Does anyone see any flaws in this plan? I realize that I have to be careful with the timing and probably add some conditional tests, as I don't want it to try to restore the differential backup before the full restore has completed. We are unlikely to change the system design, so although I welcome any responses, suggestions to eliminate the reporting database, refresh less frequently, etc., will not be helpful. I'm also thinking of trying a 3rd party product that may help, such as Idera's SQL Safe with 'Instant Restore'. Anyone have experience with that or something similar? Thanks for any ideas you may have! Scott
The site works on voting. For all helpful answers please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution you can indicate that by clicking on the check mark next to that answer.
I think the general plan is a good one. There are a few things you can do to try to get the restore process itself to run faster. I wrote an article about them [on Simple Talk][1]. In addition to what you're already doing, if you haven't tried all these, some may help. [1]:
https://www.simple-talk.com/sql/backup-and-recovery/faster-restores-best-practices-to-increase-speed/
If your database is really read-only on the report server, I would consider removing the step that restores the full backup every night. If you do a restore `WITH STANDBY` you get a read-only database to which you can append more backups. Just restore the full backup at the beginning of the week, then restore the differentials each morning on top of it. You can read more on recovery models and restoring with standby here: [
http://technet.microsoft.com/en-us/library/ms178615.aspx][1] I think your plan is sound and the article by @Grant Fritchey is good advice. [1]:
http://technet.microsoft.com/en-us/library/ms178615.aspx
16 People are following this question.
