Backup / Restore SQL Databases without the need of SMO
-
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 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 writ
-
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 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 writ