Monday, September 17, 2012

Creating a Windows Service to Automatically Restore SQL Backups.

I’ve been working on a project where I need to have SQL backups that were placed in a folder automatically restored to a SQL instance.
Creating the Windows Service itself is pretty easy – the best walk-through I found was this one on MSDN. If you are unfamiliar with Windows Services development, you might want to give that a good read, and create the sample service it guides you through.
Creating a service is pretty straightforward, and we need ours to do two basic things:

  • Monitor a folder for files 
  • Restore ‘.bak’ files to MS SQL Server

To handle the folder-watching, I’m using the NotifyFilters Enumeration and the FileSystemWatcher class from the System.IO Namespace.
So, first off, at my service’s class level, I have instantiated a static FileSystemWatcher:


Then, in my service’s OnStart method, I can interact with the watcher that I instantiated earlier:

Notice I am watching for both ‘*.bak’ and ‘*.tmp’ files. This is because the files will be copied in as ‘*.bak.tmp’ files. Renaming from ‘.tmp’ to ‘.bak’ will trigger the restore, so let’s get to the second part of the services purpose: restoring SQL .bak files.

You will see on line 10 of the above snippet, that I’m calling a method called ‘restoredb’ when the file is renamed, so let’s cover that method now.

To interact with the SQL server (which will be installed on the same machine as the service), I’m utilizing SQL Server Management Objects (or SMO for short). Before you go on, you might want to take time to read the MSDN SMO Programming Guide if you would like a deep understanding of what is happening here. Now, for the purpose of our project we need to reference a few assemblies. These assemblies are found in your SQL Server installation directory under the ‘SDK\Assemblies’ subdirectory. The full path on my machine is ‘C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies’. The specific .dll’s we need to add for the sake of this project are as follows:
Microsoft.SqlServer.ConnectionInfo Microsoft.SqlServer.Management.Sdk.Sfc Microsoft.SqlServer.Smo Microsoft.SqlServer.SmoExtended Microsoft.SqlServer.SqlEnum

You also need to have ‘using Microsoft.SqlServer.Management.Smo;’ in your using statements as well.
Now, for my restoredb method:

Please make special note of Line 7 in this snippet. My service is still somewhat incomplete, and in it’s final production implementation, it will be passed and argument to determine the ‘dbname’, however for right now, ‘DateTime.Now.Millisecond.ToString();’ gives me a nice random 3-digit number I can use for my ‘dbname’ (I’m sure there are many other [probably better] ways to do this, but it works for me).

On line 17, I’m doing the SMO equivalent of a ‘RESTORE FILELISTONLY’ T-SQL statement to determine the MDF and LDF file names, I’m storing those in a DataTable and generating the strings ‘mdfname’ and ‘ldfname’ from that so that I can execute RelocateFiles and specify where they will be stored on the new server.

Before I go any further, I should say that ‘watcherpath’, ‘log‘, dbpath’, and ‘sqlserver’ are all defined in my App.config as follows (these specific locations are temporary for now, and will move before being put into production):

These are initialzed on the C# code as follows:

So finally we can restore the database, and relocate the MDF and LDF files as desired:

Finally, to put it all together, here is the complete ‘ASRService.cs’ file:

I still have some tweaking and finalizing to do before this goes into production, but I was pretty excited today when I got this far. Hopefully this post will help out if you ever find yourself needing similar functionality.

No comments:

Post a Comment