Backup your MS SQL Server with C#

Backup your MS SQL Server with C#

Today I will describe some code snippets on how to backup your MS SQL Server database to a ‘.bak’ file using C#. Later on we will zip our .bak file using open source C# zip library as bak files seems to have high compression ratio.
Backing up files require us to connect to the database server and than use of Microsoft.SqlServer.Mangement.Smo objects to create actual backup.

Assuming we have some private fileds ready:


        private Server sqlsrv; //initialized when connection succeedes
        private string dbName;
        private string backupFile;
        private string serverName;
        private string sqlUser;
        private string sqlPass;

Lets first create a connection to our database server and initialize our Server type object:


        private bool connectToSQLServer()
        {
            try
            {
                ServerConnection serverConn = new ServerConnection(this.serverName);
                // Log in into sqlserver
                serverConn.LoginSecure = false;
                // Give the login username
                serverConn.Login = this.sqlUser;
                // Give the login password
                serverConn.Password = this.sqlPass;
                // create a new sql server object
                sqlsrv = new Server(serverConn);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
                return false;
            }
            return true;
        }

If all goes fine and we have our Server (sqlsrv) object ready we can start our backup operation:


        public bool bakBackup()
        {
            //If the connection returns false return from this method too.
            if (!connectToSQLServer())
                return false;
            try
            {
                // Create a new backup object
                Backup bkpDatabase = new Backup();
                // Set the type to database
                bkpDatabase.Action = BackupActionType.Database;
                // set the database name we want to actually backup
                bkpDatabase.Database = dbName;
                // To get the file from me actual backup create BackupDeviceItem
                BackupDeviceItem bkpDevice = new BackupDeviceItem(this.backupFile, DeviceType.File);
                // add the backup file device to our backup
                bkpDatabase.Devices.Add(bkpDevice);
                // execute the actual backup using Smo
                bkpDatabase.SqlBackup(sqlsrv);
                //verify if the file exist
                if (File.Exists(this.backupFile))
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Backup file couldn't be created" + ex.StackTrace);
                return false;
            }
        }

As a side note I would mention that the only two folders I have found on my system where I can actually perform the backup (store tha bak file) is “C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLBackup” or “C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLData” folders, this has something to do with permissions setup and I’ll will update this post once I find the solution. You can read my post on stackoverflow regarding this issue.

Assuming that all went ok, lets create a zip file which will compress our bak file to the size more than 2 times smaller than the original bak file.
For file comression I will use great C# library ‘SharpZipLib’ which you can find here.
Before using the snippet below you would have to add a reference to the library in your project.


        public static void Zip(string sourceFile, string destinationFile, int BufferSize)
        {
            FileStream fileStreamIn = new FileStream(sourceFile, FileMode.Open, FileAccess.Read);
            FileStream fileStreamOut = new FileStream(destinationFile, FileMode.Create, FileAccess.Write);
            ZipOutputStream zipOutputStream = new ZipOutputStream(fileStreamOut);

            byte[] buffer = new byte[BufferSize];

            ZipEntry entry = new ZipEntry(Path.GetFileName(sourceFile));
            zipOutputStream.PutNextEntry(entry);

            int size;
            do
            {
                size = fileStreamIn.Read(buffer, 0, buffer.Length);
                zipOutputStream.Write(buffer, 0, size);
            } while (size > 0);

            zipOutputStream.Close();
            fileStreamOut.Close();
            fileStreamIn.Close();
        }

Looks pretty easy? Well, actually it is! As a source and destination file arguments provide full path to your file (.bak) as source and (.zip) as destination and you are done.
You can of course change one name to another using simple call:


string zipFileName = fileName.Replace(".bak", ".zip");

Happy coding!

6 responses on “Backup your MS SQL Server with C#

  1. Hazem Abdul Hakeem May 25, 2009 at 10:28 am

    Thank you for your help… I really found the pieces of information presented here useful.

    I’d like to add a note:
    We should add a reference (from .Net tab) to the following:
    a) Microsoft.SqlServer.Smo
    b) Microsoft.SqlServer.ConnectionInfo

    Then we add the following two using statements:
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;

    Thanks in advance

  2. Quick Facts October 30, 2010 at 2:30 pm

    You you could make changes to the post subject title Backup your MS SQL Server with C# | Software Passion to more better for your subject you write. I loved the the writing however.

  3. TJ December 16, 2010 at 1:59 pm

    Hi there,

    I tryied this example but it returns this erros on compiling… I added all references stated… but no luck… can someone help?

    Tkz
    Error 1 The type or namespace name ‘Backup’ could not be found (are you missing a using directive or an assembly reference?) D:2 – Development1 – IG\pt.IG.SQLBackupRestore\pt.IG.SQLBackupRestore\pt.IG.SQLBackupRestore\Form2.cs 67 17 pt.IG.SQLBackupRestore

  4. admin December 16, 2010 at 2:05 pm

    Have a look here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx, then read the first comment left by Hazem 🙂

  5. Hi All October 3, 2011 at 9:02 pm

    I can not declare type server (private Server sqlsrv). I added reference Microsoft.SqlServer.Smo and I’m using the Microsoft.SqlServer.Mangement.Smo. Is there something else I should have?

  6. orfos May 22, 2012 at 5:41 pm

    We should add a reference (from .Net tab) to the following:
    Microsoft.SqlServer.Smo
    Microsoft.SqlServer.ConnectionInfo
    and
    Microsoft.SqlServer.SmoExtended.dll

Leave a Reply

Related Posts

    No Releated Posts