As a DBA, you have almost certainly taken a backup from one server for the purposes of restoration on another server. It’s a somewhat tedious and manual process, but most of us are sort of resigned to it and accept it simply as a necessary evil of the DBA role. I mean, it’s not like you can automate the entire process, right? Well, hold that thought …

Let us first go through the process of taking a SQL Server backup on one machine and restoring it on another machine. This typically happens when copying a database from a production server to restore to a test environment, or when migrating databases from one server to another:

1. Open SQL Server Management Studio (SSMS) on the ‘source’ machine. Right-click the database you want and choose the Tasks > Backup option.
2. In the Back Up Database window that opens, set the backup options you want such as whether to verify the backup file and to perform a checksum, and click OK to start the backup process.
   
3. Once the backup operation completes, copy the backup file to the new server. If the database is huge, this step and the previous one may take several hours. And during this time you have to constantly keep checking on the process to see how far along the backup or file-transfer process is. While waiting, DBA’s usually take this time to catch up on other important tasks such as checking your Facebook timeline or Twitter feed. It beats having to twiddle your thumbs waiting for the agonizingly slow progress bar to take what seems like 3 hours to move from 17% to 18%.
4. When both the backup and file transfer to the new machine is (finally!) complete, it’s time to perform the restore operation. Log in to SSMS, right-click Databases and select the Restore Databases option. Select the database you are restoring to and specify the source of your restore operation – in this case, the file you just copied over.
   

Like we said before, it is a tedious and manual process that just has to be babysat. No way around it. The solution – automation! But this involves the creation of several scripts – one to perform the backup, one to move the files from one machine to the other (and remember the files must be in shared folders/ directories), and yet another script to perform the restore. And finally, one ‘master’ script or program to call and run all the others in the proper sequence. One script to rule them all, if you will. And if you want to get fancier, set up a monitoring tool to alert you when each stage/ script is complete.

The problem isn’t that all this is impossible, it’s that for most DBA’s it usually falls into that Goldilocks zone in which it happens just rarely enough for you to never actually get round to the creation of the scripts, but also just frequently enough for it to be an annoyingly time-consuming activity every time you have to do it. And it is also likely to be one of those activities you always swear to try and automate “whenever you’ll have some free time”.

The Automated Solution

But fear not! Help is at hand for a procrastinator very busy DBA like you. With the SqlBak app, your backup+transfer+restore operations are turned into a set-and-forget setup. Install the app-side agents on your servers, and then configure them as necessary. And you don’t even have to worry about sharing folders and receiving alerts – the process will automatically move files as necessary and alert you by email. Oh, and you can even set this up in a recurring schedule, say if you always restore your end-of-week backups to a test server. The actual step at which you set up this backup+transfer+restore is shown below. You’ll notice that the tedium of all the previous steps is boiled down to a single-screen setup.

Of course, before getting to this point there are a few more steps to perform, such as installing the SqlBak app and setting up your Backup and Restore options. It is still a fully automated solution for the challenge of taking backup then restoring to another server, for the busy DBA.

Conclusion

So if you’re a DBA who wants to automate your backup and restore operations on separate machines, but are simply too busy or don’t know how to automate this process using scripts, then SqlBak offers a good solution for this need. It really is a one-time setup app that you can then call on any time you need to, without having to babysit the process.