Hi,

This is a good topic. Thank you for sharing.

In my case I needed to provide the developers the ability to restore a copy of production in the development environment. There are several databases so the developers need to specify the database to restore. The other consideration is that the permissions in Production are more restricted than the permissions in Development and when the database is restored from one SQL instance to another the loggins got messed up and need to be synchronized.

In order to accomplish the task I used a SSIS package to take advantage of parameters and a SQL Job that runs the package and can be executed by the developer using the command line. The steps in the SSIS package are:

1) Get backup info: File name, backup location and restore location

2) Copy backup file to restore location

3) Script Task to fill tsql variables

4) Generate the grant db access script using system views and store it in a variable

5) Generate the grant db roles script using system views and store it in a variable

6) Set database in single user mode with rollback immediate

7) Restore the database using the tsql stored in one of the variables

8) Remove the security coming from production

9) Restore the security using the tsql generated in steps 4 and 5

These are just the steps in general. If anybody is interested in the details of a specific step just let me know.