Restore SQL Server Database On A Schedule

One of our companies has a training environment in addition to the standard PS,DV, PY & PD environments. They have been working very hard refining their Procure-To-Process (P2P). Now that they know how they would like to handle this process, they need to train the users. That’s where the P2P Training (PTR) environment comes in.

We setup the environment and have had everything running smoothly for the last few months. However, last week we received a request to backup the data in that environment and then restore that backup copy every morning for the next 2 weeks.

It’s a really simple process, but kind of a pain that I had to manually do the restore.

Well, if you’ve been reading this blog for very long, you know that I can’t stand doing things more than once. A trained monkey, I am not. If it can be automated, I do it and it usually makes sense to do so. This was definitely one of those times.

What I ended up doing was making 2 files:

1. A bat file to call the sql file

   echo off
   echo. >> restore.log
   echo %date% %time% >> restore.log
   sqlcmd -E -d JDE_PTR -i h:\restore.sql >> restore.log
   echo %date% %time% >> restore.log
   
   

2. The sql file

   USE master
   GO
   
   ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   GO
   
   RESTORE DATABASE TestDB FROM DISK=N'H:\TestDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
   GO
   
   ALTER DATABASE TestDB SET MULTI_USER
   GO
   

Then, I just added a scheduled task that fired off the bat file every morning.

Note: When creating the scheduled task, you will need to fill in a value for the “Start in” option. I’m not sure why but it only works if you fill that in. I was using Windows Server 2008 R2 so your mileage may vary.

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

stewart@9rains.com

http://stewartschatz.com