Nightly database restores - SSIS package - SQL Server 2005

Question

We have an SSIS package that runs nightly which takes the backup of a couple of production databases, restores to a staged database, sensitive information is removed and then the backup of this staged database gets restored on another server so that the hyperion guys can run their jobs. The whole process used to take around 4 and half hours but of late the process is taking longer than that - sometimes it takes around 6 hours. Sizes of prod databases involved are 190 GB and 90 GB. We are using litespeed for both backup and restores.

I wanted to know if there is anyway to quicken this process (I know, answer to this question would be Paul Randal's fav. "Well, it depends") - is there any other better way other than SSIS package to get the same result?

Here is the requirement.

• Backup PRODDB1.SERVER1, PRODDB2.SERVER2
• Restore PRODDB2.SERVER2 on TEST.SERVER2
• Remove sensitive data on TEST.SERVER2
• Restore TEST.SERVER2 on REPORT1.SERVER3
• Write a Notification File for Hyperion guys that they can start running their jobs
• Restore PRODDB1.SERVER1 on REPORT2.SERVER3
• Run Some post restore scripts on REPORT2.SERVER3

Answer 1

Everybody uses the "It depends" response...after 10 years of hearing it, I'm sick of it!

Just some ideas here...

I'd make sure you're not doing any unnecessary file copies - back files up to their restore location.

Make sure operations are able to execute in serial in SSIS - so one task is not waiting on the other.

Put some auditing in your steps or enable package logging to see what steps are taking the majority of the job time. Spend your time optimizing those steps.

There could be room to optimize your sanitization scripts or your post restore scripts. Again - see what's taking so long and focus on the largest possible gains.