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!