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.
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 git hub here https://www.github.com/CTBlankenship/BackupRestore_Source
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 then 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 specifiy within the app.config file. I set mine up so that it is copied to by Google Drive so it is synched 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 minue 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 .back file is then deleted leaving the Scratch directory empty for the nex round of processing.
I added logging which you can view using he 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 simply delete it from the application's startup directory. I tried to make clearing the log as an option available through the interface but for some reason, the file remained "open" and accessing it from the next round of logging errored out with an I/O error saying the file is open by another process. One of you might be able to figure that out and make it possible to clear the file without accessing the file with File Explorer. Seems trivial as I type this out but the solution evaded me and I gave up ... I've spent too much time on this as it is.
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". Enchance it until your heart is content and the 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 loose as much data that 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.
Have at it ... I've had fun now it is time for a few of you to add your own personal genius to the application. So, that said, it would be cool to be able to include this form in the utilities section of my app. However, the users do not have NETWORK SERVICE credentials to the SQL Server so they cannot specify the network drive mapping, 2) allow the user to configure the a job as a windows service so it can be scheduled on a periodic basis. There is a free application out there that does all of this and more but I obviously cannot integrate it into my own app though so I'll keep this around for a while.
Charles T. Blankenship
Points of Interest
Thanks to all of you that contribute ... be gentle now.