SQL Server Backup Utility

Introduction

I initially pulled this from the internet over a decade ago and had to open it back up when my client didn't want to use SMSS. I gave him the option to backup and restore his SQL Server with a few mouse clicks.

Background

My client was too cheap to purchase a real backup/restore SQL Server application so I created/enhanced this to enable him to do it with just a few mouse clicks. If you would like to enhance the code and collaborate on potential enhancements, you can download the code from Github.

Using the Code

There are a few things you need to do to get this to work for you. The first is directory definitions:

1. A scratch pad area where the .bak file is copied prior to being compressed and copied to the destination directory. There should never be a .bak file in this directory since it is deleted after it is used to create a .zip file.
2. The location of your SQL Server database and log files (.ldf and .mdf)
3. The location directory where you want to store the compressed .bak files (which are now zip files)

Secondly, edit the main form code behind to specify a connection string for the application. I've keyed the connections to recognize machine names. So, when I copy the app to my client's machine, it knows that connection string for that implementation ... another for when I'm working with it on my development machine.

Third, edit the main form code behind to filter the available databases so that he sees only the databases that apply to his application and keep all of the other ones hidden so he doesn't make a mistake with any databases other than his own.

To backup a SQL Server, select the database to backup and press the Backup button. This backs up the database and stores its .bak file in the Scratch directory where .NET compression is used to compress it. The .bak file is compressed into a .zip file and copied to the directory you specify within the app.config file. I set mine up so that it is copied to by Google Drive so it is synced with offsite storage.

To truncate the log file, select the desired database and press the Shrink Log File.

To restore a database, select the desired .zip file (the file is named using month day year hour minute and second). It then provides a warning message that identifies the age of the file (in hours) and asked if he really wants to restore from that particular .zip file. The .zip file is then decompressed into the Scratch subdirectory. From there, script is written to backup the database from the .bak file as expected. The .bak file is then deleted leaving the Scratch directory empty for the next round of processing.

I added logging which you can view using the Show button. This is helpful since I store the SQL script used to both backup and restore the database. If there is ever a failure, it is a simple step to copy the script, drop it into a new query window in SSMS, run it and try and determine where the script failed and why.

To clear the log file, press the Show In Notepad button ... press Ctrl+A, delete the contents and save.

One glaring problem with this utility is that it demands the use of Version 11 of the Microsoft.SqlServer.Management.Sdk.Sfc file. I've discovered that if the only database you have installed on your server is 2016 or better, the only version of this file available is 12+. I had to hack my GAC so I could copy the Version 11 file and install it to the GAC on my client's machine. Version 11 is automatically installed with SQL Server 2012. If any of you gurus could figure this out, that would be grand.

I work alone so I figured I'd use you fellows to view it and perform a "code review". Enhance it until your heart is content and then push the changes to Github.

The drawbacks to this program is that it currently only has an option for a full backup. Obviously, this is not optimal since if there is a problem, the only option is to lose as much data as was entered since the last full backup. I tried to get him to purchase a "real" SQL Backup utility, but he's a cheap dude. Plus, he NEVER backed the database currently being used by the system that I'm replacing.

The Github address for this code is http://www.github.com/CTBlankenship/SQLBackupRestore. Have a go at it ... I've had fun, now it is time for a few of you to add your own personal genius to the application.

Points of Interest

I like this because I can integrate it into the utilities menu of my application. There is another option out there available for free (I didn't discover this until I'd already written my application) and can be downloaded here: I am not a paid representative for this application so I feel comfortable referencing you to it ... https://www.sqlbackupandftp.com/features.

History

• 31^st October, 2019: Initial version