Get routine backup of MSSql database using .NET application

Today, I will show you the process for “Getting Regular Backup of your application’s database”.

This code can be run in any application either in console application or in window application or in web application.

Here’s the code for Getting Routine Backup:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Threading;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Win32;

namespace BackupFullDatabase
{
    class Program
    {
        static void Main()
        {

            //This will create value entry in registry..so if your system reboot, then it will still take back up of your application using this value.
            RegistryKey rkApp = Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run", true);
            rkApp.SetValue("FullBackup", AppDomain.CurrentDomain.BaseDirectory + "BackupFullDatabase.exe");
            //write this line for console application..b'coz this will hide command prompt displaying on debug mode...so process will execute in background...
            System.Diagnostics.ProcessStartInfo prcInfo = new System.Diagnostics.ProcessStartInfo();
            prcInfo.CreateNoWindow = true;
            prcInfo.UseShellExecute = false;

            Console.WriteLine("SQL Server Backup is starting...\n");
            Console.WriteLine("Starting SQL Database Backup...\n");
            bool doDateStamp = true;

            //This will check for folder existance..if not then it will create.....
            if (!Directory.Exists("c:/Backup/"))
            {
                Directory.CreateDirectory("c:/Backup/");
            }
            string m_backupDir = "c:/Backup/";
            //These two are user defined functions...
            DeleteOldBackups();
            DoBackups(m_backupDir, doDateStamp);

            int counter = 10;
            Console.WriteLine("");
            while (counter >= 0)
            {
                Thread.Sleep(1000); // Sleep to allow for 1 second timer ticks
                counter--;
                if (counter == 0)
                {
                    DeleteOldBackups();
                }
            }
            Generate();
        }

        private static bool DoBackups(string backupDir, bool dateStamp)
        {
            bool allBackupsSuccessful = false;

            StringBuilder sb = new StringBuilder();

            // Build the TSQL statement to run against your databases.
            // SQL is coded inline for portability, and to allow the dynamic
            // appending of datestrings to file names where configured.

            sb.AppendLine(@"DECLARE @name VARCHAR(50) -- database name  ");
            sb.AppendLine(@"DECLARE @path VARCHAR(256) -- path for backup files  ");
            sb.AppendLine(@"DECLARE @fileName VARCHAR(256) -- filename for backup ");
            sb.AppendLine(@"DECLARE @fileDate VARCHAR(20) -- used for file name ");
            sb.AppendLine(@"SET @path = '" + backupDir + "'  ");
            sb.AppendLine(@"SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) ");
            sb.AppendLine(@"DECLARE db_cursor CURSOR FOR  ");
            sb.AppendLine(@"SELECT name ");
            sb.AppendLine(@"FROM master.dbo.sysdatabases ");
            sb.AppendLine(@"WHERE name NOT IN ('master','model','msdb','tempdb')  ");
            sb.AppendLine(@"OPEN db_cursor   ");
            sb.AppendLine(@"FETCH NEXT FROM db_cursor INTO @name   ");
            sb.AppendLine(@"WHILE @@FETCH_STATUS = 0   ");
            sb.AppendLine(@"BEGIN   ");

            if (dateStamp)
            {
                sb.AppendLine(@"SET @fileName = @path + @name + '_' + @fileDate + '.bak'  ");
            }
            else
            {
                sb.AppendLine(@"SET @fileName = @path + @name + '.bak'  ");
            }
            sb.AppendLine(@"BACKUP DATABASE @name TO DISK = @fileName  ");
            sb.AppendLine(@"FETCH NEXT FROM db_cursor INTO @name   ");
            sb.AppendLine(@"END   ");
            sb.AppendLine(@"CLOSE db_cursor   ");
            sb.AppendLine(@"DEALLOCATE db_cursor; ");

            string connectionStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=true";

            SqlConnection conn = new SqlConnection(connectionStr);

            SqlCommand command = new SqlCommand(sb.ToString(), conn);

            try
            {
                conn.Open();
                command.ExecuteNonQuery();
                allBackupsSuccessful = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("An error occurred while running the backup query: " + ex);
            }
            finally
            {
                try
                {
                    conn.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("An error occurred while trying to close the database connection:" + ex);
                }
            }

            return allBackupsSuccessful;
        }

        private static void DeleteOldBackups()
        {
            String[] fileInfoArr = Directory.GetFiles("c:/Backup/");

            for (int i = 0; i < fileInfoArr.Length; i++)
            {
                File.Delete(fileInfoArr[i]);
            }

        }

        public static void Generate()
        {
            int counter = 20;
            Console.WriteLine("");

            while (counter >= 0)
            {
                Thread.Sleep(1000);
                counter--;
                if (counter == 0)
                {
                    Main();
                }
            }
        }
    }
}

1. Copy and paste this code if you want to use in console application…

2. For windows application :-

do the same thing…just some minor changes there..

First of all, delete the form1 from application….and write down the code in program.cs file…

in that….copy and paste this code in class Program….delete the code available in that class before…

–> then put this line “rkApp.SetValue(“FullBackup”, Application.ExecutablePath.ToString());” in place of “rkApp.SetValue(“FullBackup”, AppDomain.CurrentDomain.BaseDirectory + “BackupFullDatabase.exe”);”

–> Remove the three line of processinfo in window application….

3. For Web Application:-

–> Create one class file. copy and paste this code start from main() in this file.

–> just create one global.asax file if not created…

in this file, write below code in Application_start() Event.

classnameabovecreated.main();

For e.g., Backup.Main();

–> So whenever application start, it will call Main Method in Backup class file. No need to create object of class file in this event…