Restore SQL Database on Schedule

Tutorial • 27 May 2020

I use a lot of demo and test environments in work and at home and frequently fill them to the brim with useless data that is only used for specific projects, and every so often I need to restore the databases for the environments to clean it up again. I’ve found that this tends to be the beginning of each month due to the way most projects go, length etc. so setting up a schedule to restore the databases made a lot of sense. It’s quite a simply process and should only really take 10 minutes depending on the size of your database(s) to set up.

1
2
3
4
5
6
7
8
-- Take the database offline before restoring
ALTER DATABASE [DatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- Restore the database
RESTORE DATABASE DatabaseName FROM DISK = '\\FileServer\directory\servername\database\database_backup.bak';

-- Bring it back online, this should be automatic but just incase.
ALTER DATABASE [DatabaseName] SET ONLINE;

The above script is quite simple. First we take the database offline to ensure that nothing is using it, we then restore the database back to the chosen backup and bring the database back online. This is the initial step and with the above you can restore a database by just pasting the script in and executing, but this still involves a lot of manual work and that’s besides the point of this entire post. We want to create it as a schedule, how do we do that?

Open SQL Server Management Studio (SMSS) and connect to your server, click on Jobs on the Object Explorer and click New Job

You’ll presented with a screen asking you for some information about the job, give it a suitable name and description so that anyone can understand what it’s doing without needing to deep dive into the T-SQL.

After filling out the required information, click on Steps then New, this will open a new window requesting information in regards to this specific step. This is where you will need the platform-specific code at the top of this post.

Click OK and you’ll be taken back to the New Job screen, click on Schedule and create a new schedule that meets your requirements. The one used in this example is on the first day of each month at 00:00:01

Click OK, and then OK again. You should now see your new job under Jobs in the Object Explorer on the right hand side of SMSS. Start the job and it will restore the database, depending on the size of your database this can be a quick process or take a while - a 2.4GB database in my environment takes around 1 minute. The benefit of this is the Job History will also log any issues that have occured with the restore or if it has completed successfully or not.

Good luck!