Simple SQL Server Database Backup Utility

Abstract

The article presents a simple utility for creating backups of SQL Server databases on a schedule. The settings specify the names of the databases for which you want to create a backup, as well as the save directory. Old databases are automatically deleted. 

Introduction

It is difficult to imagine a modern serious project, which does not use database one way or another. Web applications, data collection systems, payment systems, industrial systems - all require arrangement and usage of large amounts of data.

I often use SQL Server databases recently in my projects. Support for these databases is built into many technologies, such as ASP.NET MVC, Web API, Entity Framework and others. A simple SQL Server demo database is even often created if you start a project from a template. Creating new or editing an existing SQL Server database schema is simple. For this, there is integrated support in Visual Studio. Also, SQL Server includes built-in Management Studio IDE.

Buildup

After I launched several of my custom software projects using SQL Server, I ran into the need to periodically back up the databases. Why do one ever needs to back up the databases? We all face the unreliability of hard disks, ransomware viruses, software failures and other problems. Having a fresh backup at hand is always calmer than without it. To automatically create backups on a schedule, I include a small project in my software solution, whose task is just to create backup copies.

Having a separate project instead of enabling the backup function in the main application has the advantage that you do not need to insert the unique backup functionality into the new project every time and think about how to use it.

Implementation

The backup project is very simple, it is based on the C # console application. The application is designed for periodic launch on a schedule.

Consider the application code, it is not complicated:

using System;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using Ionic.Zip;

namespace SimpleDBBackup
{
    class Program
    {
        /* Local SQL Server connecton string */
        static string connectionString = "Server=localhost;Integrated Security=True";

        //Optional:connect using credentials
        //static string connectionString = "Server=localhost;user id=user2018;password=MYDBPASSWORD;";

        /* Database names to backup */
        static string[] saDatabases = new string[] { "shop", "frontend", "accounting" };

        /* Backup directory. Please note: Files older than DeletionDays old will be deleted automatically */
        static string backupDir = @"C:\DB_Backups";

        /* Delete backups older than DeletionDays. Set this to 0 to never delete backups */
        static int DeletionDays = 10;



        static Mutex mutex = new Mutex(true, "Global\\SimpleDBBackupMutex");
        static void Main(string[] args)
        {
            // allow only single instance of the app
            if (!mutex.WaitOne(TimeSpan.Zero, true))
            {
                Console.WriteLine("Program already running!");
                return;
            }

            if (DeletionDays > 0)
                DeleteOldBackups();

            DateTime dtNow = DateTime.Now;

            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();

                    foreach (string dbName in saDatabases)
                    {
                        string backupFileNameWithoutExt = String.Format("{0}\\{1}_{2:yyyy-MM-dd_hh-mm-ss-tt}", backupDir, dbName, dtNow);
                        string backupFileNameWithExt = String.Format("{0}.bak", backupFileNameWithoutExt);
                        string zipFileName = String.Format("{0}.zip", backupFileNameWithoutExt);

                        string cmdText = string.Format("BACKUP DATABASE {0}\r\nTO DISK = '{1}'", dbName, backupFileNameWithExt);

                        using (SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection))
                        {
                            sqlCommand.CommandTimeout = 0;
                            sqlCommand.ExecuteNonQuery();
                        }

                        using (ZipFile zip = new ZipFile())
                        {
                            zip.CompressionLevel = Ionic.Zlib.CompressionLevel.BestCompression;
                            zip.AddFile(backupFileNameWithExt);
                            zip.Save(zipFileName);
                        }
                        
                        File.Delete(backupFileNameWithExt);
                    }

                    sqlConnection.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            mutex.ReleaseMutex();
        }

        static void DeleteOldBackups()
        {
            try
            {
                string[] files = Directory.GetFiles(backupDir);

                foreach (string file in files)
                {
                    FileInfo fi = new FileInfo(file);
                    if (fi.CreationTime < DateTime.Now.AddDays(-DeletionDays))
                        fi.Delete();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }
}

The application program consists of several main blocks:

1. The Settings block
2. Main function, in which the actual backup is created
3. The DeleteOldBackups function, in which old backups are deleted

Consider these blocks in more detail:

The Settings block

/* Local SQL Server connecton string */
static string connectionString = "Server=localhost;Integrated Security=True";

//Optional:connect using credentials
//static string connectionString = "Server=localhost;user id=user2018;password=MYDBPASSWORD;";

Here you specify the Connection String to the database. In the simplest case, if you use a local server with the default settings, the first line is enough. If you want to connect to the server with login and password, uncomment and modify the second line.

/* Database names to backup */
static string[] saDatabases = new string[] { "shop", "frontend", "accounting" };

This is an array with the database names on the server, for which you want to make a backup. If you need to make a backup of one database, you can do this:

static string[] saDatabases = new string[] { "shop" };

/* Backup directory. Please note: Files older than DeletionDays old will be deleted automatically */
static string backupDir = @"C:\DB_Backups";

This is the name of the directory for storing backups. Please note that this directory should be used only for storing backups and not for anything else. Why? Because the files from this directory will be deleted after a certain period of rotation of the backup copies (see below)

/* Delete backups older than DeletionDays. Set this to 0 to never delete backups */
static int DeletionDays = 10;

This is the number of days that old databases will be deleted.

The Main function

static Mutex mutex = new Mutex(true, "Global\\SimpleDBBackupMutex");
static void Main(string[] args)
{
    // allow only single instance of the app
    if (!mutex.WaitOne(TimeSpan.Zero, true))
    {
        Console.WriteLine("Program already running!");
        return;
    }

At the beginning of the Main function, I installed the Mutex (Mutually Exclusive) object. This object can exist only in a single instance. In this program, I use Mutex to prevent the program from starting a second time. Using Mutex objects, you can do many interesting and fascinating things for the interaction between processes and threads.

The next interesting block in the Main function is the connection to the database and the execution of the backup command:

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
    sqlConnection.Open();

  ...

        using (SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection))
        {
            sqlCommand.CommandTimeout = 0;
            sqlCommand.ExecuteNonQuery();
        }

      ...

    sqlConnection.Close();
}

The names of the backup files themselves are made up of the current date and time:

string backupFileNameWithoutExt = String.Format("{0}\\{1}_{2:yyyy-MM-dd_hh-mm-ss-tt}", backupDir, dbName, dtNow);

Backup copies are used infrequently, so they are better compressed to save disk space. For these purposes, we suggest using the DotNetZip package:

using (ZipFile zip = new ZipFile())
{
    zip.CompressionLevel = Ionic.Zlib.CompressionLevel.BestCompression;
    zip.AddFile(backupFileNameWithExt);
    zip.Save(zipFileName);
}

Do not forget to close the connection, delete unnecessary files, and handle exceptions!

DeleteOldBackups Function

Here everything is quite simple: we get a list of files with the creation date attributes, and we delete all files whose creation date is earlier than the current date minus the specified number of days:

static void DeleteOldBackups()
{

    try
    {
        string[] files = Directory.GetFiles(backupDir);
        foreach (string file in files)
        {
            FileInfo fi = new FileInfo(file);
            if (fi.CreationTime < DateTime.Now.AddDays(-DeletionDays))
                fi.Delete();
        }
    }

    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

How to use the program

After the program is configured and compiled in the Release version, it is necessary to copy it together with the libraries in a separate directory, for example, C:\SimpleDbBackup, also create a directory for the databases (in this example it's C:\DB_Backups)

Now you need to create a periodic task with Windows Task Scheduler so that it runs on a schedule every day or twice a day, as you like. I suggest now you to create a task manually. Perhaps in the future I will add the function of creating a scheduled task in the code of the main program, but for now, I propose you do it manually. 

I will be happy with any comments and suggestions. If you wish to add some missing functions to the program by yourself. feel free to clone a github repo.

Good luck!

Version History

2018-06-05 First version published