How to automate the process of restoring a SQL database

It’s very likely that you frequently refresh a development or test environment with recent production SQL server database backups. However, depending on the size and contents of a production database, this process might take a large amount of disk space and be pretty slow since the SQL server database backup needs to be fully restored. This is where ApexSQL Restore comes into play

ApexSQL Restore a SQL database restoring tool virtually restores SQL databases, native and natively compressed backups and backup sets created in SQL Server. This includes full and transaction log backups as well as differential backups.

The given backup sets will be restored as a fully usable online database which is accessible via SQL Server Management Studio, Visual Studio, or any other third-party tool.

Automating a SQL database restoring process can be done by creating an automated task to perform a virtual SQL backup restore regularly (weekly, daily, or even several times a day) and this will save you a lot of time.

ApexSQL Restore comes with the support for the CLI which allows you to automate a SQL database restoring process.

By using the available switches, you can easily create a command appropriate for your scenario.

For example, to virtually restore the Production.bak SQL database backup file stored on the root of the C drive as a database named ProductionTest to a SQL instance named SkyrimSQL using Windows authentication, you will use the following command:

ApexSQLRestore.com /s: SkyrimSQL /b: C:Production.bak /a: /d: ProductionTest

After that, refreshing the list of SQL databases in the Object Explorer pane shows the virtually restored backup as a new database.

Further on, to virtually restore a SQL database backup set containing several backup files including full and transaction log backups, you can use a semicolon ( ; ) to separate the selected backup files. For instance:

ApexSQLRestore.com /s: SkyrimSQL /b: C:Production.bak;Production_LogBackup1.bak;AW_LogBackup2.bak /a: /d: ProductionTest

Finally, if you want to print all the CLI messages which are suppressed by default, you can use the /v (/verbose) switch. For instance:

ApexSQLRestore.com /s: SkyrimSQL /b: C:Production.bak /a: /d: ProductionTest /v

Once you’ve created an appropriate CLI command, save it as a batch file (*.bat) file.

Schedule a SQL database restoring process by using Task Scheduler

To create a scheduled task go to Control Panel ➜ Administrative Tools ➜ Task Scheduler:

1. In the Task Scheduler window select the Create Basic Task option:

   

2. In the Create Basin Task Wizard name a new task and click Next:

   

3. In the Task Trigger window specify a task frequency and click Next:

   

   

4. In the Action window select the Start a program option and click Next:

   

5. In the Start a program window browse to a previously created .bat file and click Next:

   

6. Review the Summary and click Finish:

   

Schedule a SQL database restoring process by using SQL Server Agent

To schedule a scheduled SQL Server Agent job:

1. Go to SQL Server Agent in the Object Explorer pane, right click the Jobs node and select the New Job option:

   

2. In the New Job wizard under the General tab specify a new name for a job:

   

3. Under the Steps tab from the Type drop down list select the CmdExec option. Click the Open button and browse to a saved .bat file:

   

4. Under the Schedules tab specify a job’s frequency and click OK:

   

To start a job right click a created job from the Jobs node and select the Start job at step option: