script it…walk away.
Over the last a couple of weeks I started reviewing an automated restore solution I had been playing around with for a couple years. RedGate SQL Backup had thrown some errors and since we couldn’t go without backups while waiting on RedGate support we decided to move ALL backup operations to Ola Hallengren’s scripts. We also decided to ditch RedGate because they sent us back a list of reported bugs we had submitted and told us they aren’t going to fix it. So in other words “despite the fact that you have multiple licenses of multiple softwares across many servers, we feel your issues as a customer aren’t important to us as a software company.” With that we decided to roll our own solutions that we can customize for our environment…
Since we were moving away from RedGate SQL Monitor in favor of our own home grown monitoring, we figured why not phase out SQL Backup as well? We had been using Ola’s scripts for years on most of our servers, but we were relying on RedGate SQL Backup for automated restore jobs…
A couple years ago I wrote an article for RedGate SimpleTalk that utilized a script to automatically restore and refresh a staging environment, and I put it out on github for the community to enjoy. A couple years later I had found a couple pull requests and some good ideas.
Recently on March 7 2017 the Ozar Unlimited posted about their version of the Backup & Restore script that had come from GregWhiteDBA’s MS SQL Autorestore! Needless to say I was pretty excited to see some new ideas for automated restores. The Ozar team’s script was a very well written script and immediately I began to test as a viable solution for our automated restores, I also submitted some bugs and fixes via Github.
Luckily since all of our solutions were based on the same Ola Hallengren’s script I knew exactly where to look for some bugs that had come up in my own development and I was able to rather easily add the ability to process differential restores as well as printing out the commands (for the cautious DBA). Adding the differential option was pretty important for our usage since we wanted to be able to test our backups every day, and having the ability to print out the commands was also something I felt really needed to be present. Some people out there, myself included, like to go through things manually, step by step if needed for thorough testing.
One of the things that was really cool about reading the Ozar team’s scripts was that I was able to see a problem in my own scripts that I had been encountering. Due to our transaction log and full backup schedules, there is an edge case where during the full backup, a log backup executes and get’s timestamped by Ola’s scripts after the full backup, however it’s last_lsn is < the full backup’s last_lsn. This would throw us a warning message during runtime, but when scheduled as an agent job it would cause the job to send a failure email. By not comparing the date for the files to restore, we can compare the LSNs and avoid this problem all together.
Some of the things you can do with the script so far?
I come across a lot of instances where I need to configure mirroring and utilizing this script to restore a database and bring it’s logs current has made things much faster. I configured mirroring for 4 databases last night in about half the time it would have taken me in the past. I used a script to disable the backup jobs on the primary server, then I ran the following command
EXEC dbo.sp_DatabaseRestore @Database = 'DatabaseName', @BackupPathFull = '\StorageServerLocationDatabaseNameFULL', @BackupPathDiff = '\StorageServerLocationDatabaseNameDIFF', @BackupPathLog = '\StorageServerLocationDatabaseNameLOG', @RestoreDiff = 1, @ContinueLogs = 0, --@RunRecovery = 1, --@TestRestore = 1, --@RunCheckDB = 1, @Debug = 0;
Once this finishes I was able to run through the mirroring script sand boom, done
Schedule an automated restore job!
This was our main motivation for getting this done, and this was super easy. You pretty much take that command referenced above, and adjust to taste. In our case, we have a dedicated server for testing backups, so we want to put the database in an operational state, then execute DBCC against it, then drop. The @RunRecovery, @RunCheckDB and @TestRestore options take care of that. Once you have the command adjusted to what you want to accomplish you can stick it in a SQL Agent Job and WIN! (I had a screenshot here that was lost during the migration, so use your imagination).
Some future enhancements:
I’d like to add the capability to add in a date time and execute a point in time restore. The script from my repository has this feature so porting it over shouldn’t be too hard. I think also modifying the script so that the backup paths can read the @DatabaseName variable and auto populate, as well as the capability to run this for multiple databases in one fell swoop would make this script even better.
My fixes and enhancements were merged today and mentioned by Erik at brentozar.com