locked
MSSql server backup and restore using C#

    Question

  • Hi, 

     

    I want to take the backup and restore the backup file using C# for SQL Server 2008

    Monday, December 27, 2010 8:43 AM

Answers

  • Hi bdnp:


    Welcome you to MSDN Forum!


    This issue can be resolved in Microsoft SQL Server 2005.
    Here are the

    demons of your threads:
        public void BacupDataBase()
        {
          SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
          SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
          bool b = true;
          try
          {
            // oSQLServer.LoginSecure = false; // if use Windows Authentication,please set it true. If use SQL Server Authentication,please use false
            oSQLServer.LoginSecure = true;
            oSQLServer.Connect("localhost", "", "");
            oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
            oBackup.Database = "DataBaseName";
            oBackup.Files = dlg.FileName;
            oBackup.BackupSetName = "SetYourName";
            oBackup.BackupSetDescription = "Backup a Database";
            oBackup.Initialize = true;
            oBackup.SQLBackup(oSQLServer);
          }
          catch (Exception ex)
          {
            b = false;
            MessageBox.Show(ex.Message);
           }
          finally
          {
            oSQLServer.DisConnect();
            Cursor.Current = oldCursor;
            if (b)
            {
              MessageBox.Show("Backup Successful!");
            }
            else
            {
              MessageBox.Show("Backup Failed!!");
            }
          }
        }
    
        public void RestoreDatabase()
        {
          SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
          SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
          try
          {
            oSQLServer.LoginSecure = false;
            oSQLServer.Connect(server, uid, pwd);
            oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
            oRestore.Database = database;
            oRestore.Files = url;//@"d:\Northwind.bak"; 
            oRestore.FileNumber = 1;
            oRestore.ReplaceDatabase = true;
            oRestore.SQLRestore(oSQLServer);
            return "OK";
          }
          catch (Exception e)
          {
            return "Restore Failed";
            throw;
          }
          finally
          {
            oSQLServer.DisConnect();
          }
        }
    


    If you use SQL Server 2008 , I suggest you to create a procedure to backup or restore your database. The feature above has been deleted. Here is the description in the msdn :
    "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. "


    You can see this infomation by click the following hyperlink:
    http://msdn.microsoft.com/en-us/library/ms140730(SQL.90).aspx

    I hope this will help you.

    Best Regards.

    • Marked as answer by Neddy Ren Tuesday, January 04, 2011 6:55 AM
    Tuesday, December 28, 2010 4:59 AM
  • Hi bdnp:

    I agree with Love Dot Net.

    If you have any question with SQL SERVER , i suggest you ask for help in the following forum:

    http://social.msdn.microsoft.com/Forums/eu/sqlintegrationservices/threads

    This may give you better support !

    Please mark as answer if it helps you!

    Best Regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the C# Forum! If you have any feedback, please tell us.
    • Marked as answer by Neddy Ren Tuesday, January 04, 2011 6:56 AM
    Friday, December 31, 2010 3:23 AM

All replies

  • Check following link where SQL server 2005 has been considered:

    http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx

     

    Hope this helps.

    Please Mark as Answer if it helped you.


    Santosh.
    Monday, December 27, 2010 8:53 AM
  • Hi Santosh,

     

    I had tried with this link but no success. :(   Unable to perform the backup and Restore database. 

     

    Thanks

    Monday, December 27, 2010 9:21 AM
  • Can we know what is the problem exactly?

    Santosh.
    Monday, December 27, 2010 9:31 AM
  • It says unable to connect to server. :(
    Monday, December 27, 2010 9:50 AM
  • Ensure that the credentials you are providing to the server are correct.
    Santosh.
    Monday, December 27, 2010 9:55 AM
  • Yes,  server credentials  are correct. my Server is running in mix mode.
    Monday, December 27, 2010 9:56 AM
  • hi, now i am getting the error "Backup failed for Server 'server-XXX'". Please help
    Monday, December 27, 2010 10:46 AM
  • If I am catching you correctly, You must be using SMO, isn't it?

    If yes, then check if it has been installed correctly.

    It would be much better if you install 2008 version of the SMO Components.

    You can get it here

    Please Mark as Answer if it helped you.


    Santosh.
    Monday, December 27, 2010 11:22 AM
  • yes i am using SMO. 
    Monday, December 27, 2010 12:43 PM
  • Hello,

    Create a stored procedure to perform the backup.

    Execute the stored procedure from within your application.

    We have done it before, it works 100%.

    Please let me know if you require any more help, also, please mark as Answer if you find this helpful.

    Monday, December 27, 2010 10:35 PM
  • Hi bdnp:


    Welcome you to MSDN Forum!


    This issue can be resolved in Microsoft SQL Server 2005.
    Here are the

    demons of your threads:
        public void BacupDataBase()
        {
          SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
          SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
          bool b = true;
          try
          {
            // oSQLServer.LoginSecure = false; // if use Windows Authentication,please set it true. If use SQL Server Authentication,please use false
            oSQLServer.LoginSecure = true;
            oSQLServer.Connect("localhost", "", "");
            oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
            oBackup.Database = "DataBaseName";
            oBackup.Files = dlg.FileName;
            oBackup.BackupSetName = "SetYourName";
            oBackup.BackupSetDescription = "Backup a Database";
            oBackup.Initialize = true;
            oBackup.SQLBackup(oSQLServer);
          }
          catch (Exception ex)
          {
            b = false;
            MessageBox.Show(ex.Message);
           }
          finally
          {
            oSQLServer.DisConnect();
            Cursor.Current = oldCursor;
            if (b)
            {
              MessageBox.Show("Backup Successful!");
            }
            else
            {
              MessageBox.Show("Backup Failed!!");
            }
          }
        }
    
        public void RestoreDatabase()
        {
          SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
          SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
          try
          {
            oSQLServer.LoginSecure = false;
            oSQLServer.Connect(server, uid, pwd);
            oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
            oRestore.Database = database;
            oRestore.Files = url;//@"d:\Northwind.bak"; 
            oRestore.FileNumber = 1;
            oRestore.ReplaceDatabase = true;
            oRestore.SQLRestore(oSQLServer);
            return "OK";
          }
          catch (Exception e)
          {
            return "Restore Failed";
            throw;
          }
          finally
          {
            oSQLServer.DisConnect();
          }
        }
    


    If you use SQL Server 2008 , I suggest you to create a procedure to backup or restore your database. The feature above has been deleted. Here is the description in the msdn :
    "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. "


    You can see this infomation by click the following hyperlink:
    http://msdn.microsoft.com/en-us/library/ms140730(SQL.90).aspx

    I hope this will help you.

    Best Regards.

    • Marked as answer by Neddy Ren Tuesday, January 04, 2011 6:55 AM
    Tuesday, December 28, 2010 4:59 AM
  • Hi Neddy,

     

    Using BacupDataBase method following error message is shown.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\SamplingUnit.bak'. Operating system error 5(Access is denied.).
    [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

     

     

    Thanks

    Tuesday, December 28, 2010 9:08 AM
  • Hi bdnp,

    You cannot perform backup directly to the root drive C:\, or any other drive for that matter.

    Put it in a sub folder, for example, "C:\Backups\SamplingUnit.bak"

    Make sure that SQL Server Service Account has write permissions to that folder!

    Please mark as an answer if that helps.

    • Proposed as answer by Neddy Ren Tuesday, January 04, 2011 6:57 AM
    Tuesday, December 28, 2010 11:09 AM
  • Hi bdnp:

    I agree with Love Dot Net.

    If you have any question with SQL SERVER , i suggest you ask for help in the following forum:

    http://social.msdn.microsoft.com/Forums/eu/sqlintegrationservices/threads

    This may give you better support !

    Please mark as answer if it helps you!

    Best Regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the C# Forum! If you have any feedback, please tell us.
    • Marked as answer by Neddy Ren Tuesday, January 04, 2011 6:56 AM
    Friday, December 31, 2010 3:23 AM
  •  protected void btnBackUp_Click(object sender, EventArgs e)
            {

                Backup bkp = new Backup();

               // this.Cursor = Cursors.WaitCursor;
              //this.dataGridView1.DataSource = string.Empty;
                try
                {
                    string fileName = this.txtFileName.Text;
                    string databaseName = this.txtFileName.Text.ToString();

                    fileName = Server.MapPath(@"backupfolder\" + fileName);

                    bkp.Action = BackupActionType.Database;
                    bkp.Database = databaseName;
                    bkp.Devices.AddDevice(fileName, DeviceType.File);
                  

                    //bkp.PercentCompleteNotification = 10;
                    //bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);

                    bkp.SqlBackup(srv);
                    MessageBox.Show("Database Backed Up To: " + fileName, "SMO Demos");
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }


            }

            protected void DDlDataBase_SelectedIndexChanged(object sender, EventArgs e)
            {
                if (DDlDataBase.SelectedValue != "0")
                {
                    txtFileName.Text = DDlDataBase.SelectedItem.ToString();

                }


            }


        }
    }
    Regards, Nithin Varghese Mob:9895940452
    • Proposed as answer by Neddy Ren Tuesday, January 04, 2011 6:57 AM
    Monday, January 03, 2011 9:35 AM
  •  What is SQLDMO Namespace? could u give me
    Regards, Nithin Varghese
    Thursday, March 24, 2011 7:11 AM