Hi
The process you're using sounds like alot of work. Perhaps you might want to investigate developing a log-shipping process for this. Not sure if I can add any more value to the previous comments, but my manual process for this would be:
1. Determine the space used by the db (sp_helpdb '[DATABASE_NAME]') on the Prod Server
2. Determine if the target machine (S1 in your case) has enough space for the full files
3. If not:
- script the Indexes as DROP on the Prod server <Script_1>
- script the Indexes as CREATE on the Prod Server <Script_2>
- script the Indexes as CREATE on the Prod Server WITH DATA | PAGE COMPRESSION <Script_3>
4. Run the DROP Script on Prod Before backing up (If I need to tell you to do this during off-peak hours, then you really shouldn't be doing this 🙂
5. Run the Backup with max compression (longer time to backup, smaller backup file created)
6. Run <Script_2> to re-create the Indexes on Prod (Remember to include the Update Stats command)
7. Copy the Backup file to the Target Machine or even better a shared Network location
8. Restore db as per normal on the Target Machine
9. Run <Script_3> to create the Indexes with Compression on the target machine. Your restored db should now be smaller than your Prod version
A few things to note:
1. This process is not fool proof, please test it first on two other servers.
2. You will have to ensure at some point that disk space is added to your target machine to match your prod server.
3. Avoid using DBCC shrinkfile | Shrink Database; this eventually causes more issues than it solves.
4. I have not taken into account the version of SQL you are using so the key to a successful implementation of this process is Test, Test, Test
5. Creating Indexes with COMPRESSION hint, may make your db footprint smaller but it also increases CPU utilisation, so ensure that the Target Machine's CPU and RAM can handle this.
Hope this helps you, I've been there where business refuses to fork out extra cash for more disk space. Eventually though they will have to or risk not being able perform this type of constant restore.