title: Nightly database restores - SSIS package - SQL Server 2005
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>Here is the requirement. </p>
  
  <ul>
  <li>Backup PRODDB1.SERVER1, PRODDB2.SERVER2</li>
  <li>Restore PRODDB2.SERVER2 on TEST.SERVER2</li>
  <li>Remove sensitive data on TEST.SERVER2</li>
  <li>Restore TEST.SERVER2 on REPORT1.SERVER3</li>
  <li>Write a Notification File for Hyperion guys that they can start running their jobs</li>
  <li>Restore PRODDB1.SERVER1 on REPORT2.SERVER3</li>
  <li>Run Some post restore scripts on REPORT2.SERVER3</li>
  </ul>