SQL Server Backup file in standard Zip format


Introduction

This is SQL Server Backup and restore tool, the system will store the backup files in standard Zip format, the user-friendly screen let you backup and restore SQL Server database to local harddisk or remote network driver easily and quickly.The program can restore database easily.

To do:

  1. First Enter Your Sql Server username and password on corresponding Text Box.
  2. Click on Backup Button.
  3. It will take backup on folder ("application path"\Backup). if folder not found then program will create folder automatically  in application path.
  4. After finish the job then check bkpfile on folder "Application path \Backup" bkp file created or not.

Important Functions

Add reference to SQL-DMO dll

You can do this by right clicking the project in Solution Explorer, then selecting 'Add Reference', COM components and the latest version of "Microsoft SQLDMO Object Library".

Available Server

public void dIsplayServerList(ComboBox cboListName)

{

    try

    {

        SQLDMO.Application oSQLServerDMOApp = new SQLDMO.Application();

        Info.informationLayer info = new Info.informationLayer();

                

        SQLDMO.NameList oNameList;

        oNameList = oSQLServerDMOApp.ListAvailableSQLServers();

        for (int intIndex = 0; intIndex <= oNameList.Count - 1; intIndex++)

        {

            if (oNameList.Item(intIndex as object) != null)

            {

                cboListName.Items.Add(oNameList.Item(intIndex).ToString());

            }

        }

        if (cboListName.Items.Count > 0) cboListName.SelectedIndex = 0;

        else cboListName.Text = "(Local)";

    }

    catch

    {

    }

}

 

Available databases 

 

public void dIsplayDatabases(ComboBox cboDatabase,Info.informationLayer info)

{

    try

    {

        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();

 

        cboDatabase.Items.Clear();

        SQLServer.Connect(info.strServerName,info.strLoginName,info.strPwd);

        foreach (SQLDMO.Database db in SQLServer.Databases)

        {

            if (db.Name != null)

                cboDatabase.Items.Add(db.Name);

        }

        cboDatabase.Sorted = true;

        if (cboDatabase.Items.Count == 0)cboDatabase.Text = "<No databases found>";

    }

    catch (Exception err)

    {

        info.ErrorMessageDataLayer = err.Message;

    }

}

 

Create backup in zip format : 

 

public void cReateSQLDatabaseBackup(InfoSQLDMO.informationLayer InSQLDMO)

{//This is for Creating Database Backup.

     try

     {

         DateTime dt = DateTime.Now;

         String[] format = { "dd-MM-yy" };

         string date;

         date = dt.ToString(format[0], DateTimeFormatInfo.InvariantInfo);

         string FileName = "bkp" + InSQLDMO.strdbName + date + ".bkp";

         string myDocPath = InSQLDMO.strmyDocPath;

         FileName = myDocPath + "\\Backup\\" + FileName;

         //  this.txtFile.Text = FileName;

         if (Directory.Exists(myDocPath + "\\Backup"))

         {

             if (File.Exists(FileName) == false)

             {

                 SQLDMO._SQLServer SqlSever = new SQLDMO.SQLServerClass();

                 SqlSever.Connect(InSQLDMO.strServerName, InSQLDMO.strLoginName, InSQLDMO.strPwd);

                 SQLDMO.Backup bak = new SQLDMO.BackupClass();

                 bak.Devices = bak.Files;

                 bak.Files = FileName;

                 bak.Database = InSQLDMO.strdbName;

                 bak.SQLBackup(SqlSever);

                 string DestFile = myDocPath + "\\Backup" + "\\" + "bkp" + InSQLDMO.strdbName+ date + ".zip";

                 zIpDatabseFile(FileName, DestFile);

                 if (File.Exists(FileName))

                 {

                     File.Delete(FileName);

                 }

                 InSQLDMO.ErrorMessageDataLayer = "Database Backup Process " + InSQLDMO.strdbName + " Sucessfully Completed"; 

             }

             else

             {

                 InSQLDMO.ErrorMessageDataLayer = "The Backup file " + InSQLDMO.strdbName + " is already  exists !. Do You Want to Continue..... '"; 

             }

         }

         else

         {

             System.IO.Directory.CreateDirectory(myDocPath + "\\Backup");

             SQLDMO._SQLServer SqlSever = new SQLDMO.SQLServerClass();

             SqlSever.Connect(InSQLDMO.strServerName, InSQLDMO.strLoginName, InSQLDMO.strPwd);

             SQLDMO.Backup bak = new SQLDMO.BackupClass();

             bak.Devices = bak.Files;

             bak.Files = FileName;

             bak.Database = InSQLDMO.strdbName;

             bak.SQLBackup(SqlSever);

             string DestFile = myDocPath + "\\Backup" + "\\" + "bkp" + date + ".zip";

             zIpDatabseFile(FileName, DestFile);

             if (File.Exists(FileName))

             {

                 File.Delete(FileName);

             }

             InSQLDMO.ErrorMessageDataLayer = "Backup Process " + InSQLDMO.strdbName + " Sucessfully Completed";

         }

     } 

     catch (Exception err)

     {

         InSQLDMO.ErrorMessageLogicLayer = err.Message;

     }

}

 

Restore database in zip format: 

 

public void cReateSQLDatabaseBackup(InfoSQLDMO.informationLayer InSQLDMO)

{//This is for Creating Database Backup.

    try

    {

        DateTime dt = DateTime.Now;

        String[] format = { "dd-MM-yy" };

        string date;

        date = dt.ToString(format[0], DateTimeFormatInfo.InvariantInfo);

        string FileName = "bkp" + InSQLDMO.strdbName + date + ".bkp";

        string myDocPath = InSQLDMO.strmyDocPath;

        FileName = myDocPath + "\\Backup\\" + FileName;

        //  this.txtFile.Text = FileName;

        if (Directory.Exists(myDocPath + "\\Backup"))

        {

            if (File.Exists(FileName) == false)

            {

                SQLDMO._SQLServer SqlSever = new SQLDMO.SQLServerClass();

                SqlSever.Connect(InSQLDMO.strServerName, InSQLDMO.strLoginName, InSQLDMO.strPwd);

                SQLDMO.Backup bak = new SQLDMO.BackupClass();

                bak.Devices = bak.Files;

                bak.Files = FileName;

                bak.Database = InSQLDMO.strdbName;

                bak.SQLBackup(SqlSever);

                string DestFile = myDocPath + "\\Backup" + "\\" + "bkp" + InSQLDMO.strdbName+ date + ".zip";

                zIpDatabseFile(FileName, DestFile);

                if (File.Exists(FileName))

                {

                    File.Delete(FileName);

                }

                InSQLDMO.ErrorMessageDataLayer = "Database Backup Process " + InSQLDMO.strdbName + " Sucessfully Completed"; 

            }

            else

            {

                InSQLDMO.ErrorMessageDataLayer = "The Backup file " + InSQLDMO.strdbName + " is already  exists !. Do You Want to Continue..... '"; 

            }

        }

        else

        {

            System.IO.Directory.CreateDirectory(myDocPath + "\\Backup");

            SQLDMO._SQLServer SqlSever = new SQLDMO.SQLServerClass();

            SqlSever.Connect(InSQLDMO.strServerName, InSQLDMO.strLoginName, InSQLDMO.strPwd);

            SQLDMO.Backup bak = new SQLDMO.BackupClass();

            bak.Devices = bak.Files;

            bak.Files = FileName;

            bak.Database = InSQLDMO.strdbName;

            bak.SQLBackup(SqlSever);

            string DestFile = myDocPath + "\\Backup" + "\\" + "bkp" + date + ".zip";

            zIpDatabseFile(FileName, DestFile);

            if (File.Exists(FileName))

            {

                File.Delete(FileName);

            }

            InSQLDMO.ErrorMessageDataLayer = "Backup Process " + InSQLDMO.strdbName + " Sucessfully Completed";

        }

    }

    catch (Exception err)

    {

        InSQLDMO.ErrorMessageLogicLayer = err.Message;

    }

}

 

Create file a zip : 

 

private void zIpDatabseFile(string srcPath, string destPath)

{//This is for  Zip a File

    byte[] bufferWrite;

    FileStream fsSource;

    FileStream fsDest;

    GZipStream gzCompressed;

    fsSource = new FileStream(srcPath, FileMode.Open, FileAccess.Read, FileShare.Read);

    bufferWrite = new byte[fsSource.Length];

    fsSource.Read(bufferWrite, 0, bufferWrite.Length);

    fsDest = new FileStream(destPath, FileMode.OpenOrCreate, FileAccess.Write);

    gzCompressed = new GZipStream(fsDest, CompressionMode.Compress, true);

    gzCompressed.Write(bufferWrite, 0, bufferWrite.Length);

    fsSource.Close();

    gzCompressed.Close();

    fsDest.Close();

}

 

Create  unzip  a  file :

 

private void uNzIpDatabaseFile(string SrcPath, string DestPath)

{// This is for unzip a files.

    byte[] bufferWrite;

    FileStream fsSource;

    FileStream fsDest;

    GZipStream gzDecompressed;

    fsSource = new FileStream(SrcPath, FileMode.Open, FileAccess.Read, FileShare.Read);

    gzDecompressed = new GZipStream(fsSource, CompressionMode.Decompress, true);

 

    bufferWrite = new byte[4];

    fsSource.Position = (int)fsSource.Length - 4;

    fsSource.Read(bufferWrite, 0, 4);

    fsSource.Position = 0;

    int bufferLength = BitConverter.ToInt32(bufferWrite, 0);

 

    byte[] buffer = new byte[bufferLength + 100];

    int readOffset = 0;

    int totalBytes = 0;

 

    while (true)

    {

        int bytesRead = gzDecompressed.Read(buffer, readOffset, 100);

 

        if (bytesRead == 0)

            break;

 

        readOffset += bytesRead;

        totalBytes += bytesRead;

    }

 

    fsDest = new FileStream(DestPath, FileMode.Create);

    fsDest.Write(buffer, 0, totalBytes);

 

    fsSource.Close();

    gzDecompressed.Close();

    fsDest.Close();

}