none
Backing up database from C# code RRS feed

  • Question

  • I have this routine to backup my database. It compiles and runs. I don't get any exception but the date on the backup file does not change and as I said I don't get any feedback. I need help here:

    private void pushBackTables_pg2_Click(object Sender, EventArgs e)
            {
                // read connectionstring from config file
                DoServer.GetSrvConnOrSwitch("ComeAndGetThr");
                using (SqlConnection conn = new SqlConnection(Globals.srv.ConnectionContext.ConnectionString))
                {
                    // read backup folder from config file ("C:\\VCSharp_Projects\\ComeAndGet\\ComeAndGet\\ConfigFile\\ConfigurationFile.config")
                    var backupFolder = ConfigurationManager.AppSettings["C:\\VCSharp_Projects\\ComeAndGet\\ComeAndGet\\ConfigFile\\ConfigurationFile.config"];
                    // set backupfilename 
                    var backupFileName = String.Format("ComeAndGetThr.bak");
                    using (var connection = new SqlConnection(Globals.srv.ConnectionContext.ConnectionString))
                    {
                        var query = String.Format("BACKUP DATABASE [ComeAndGetThr] TO DISK='C'");
                        using (var command = new SqlCommand(query, connection))
                        {
                            connection.Open();
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }                         // pushBackTables_pg2_Click

    I got this code from this website. I found his code incomplete with some essential details omitted. I think my code does not work and no backup happened, but why?

    - MyCatAlex


    • Edited by MyCatAlex Sunday, September 16, 2018 11:29 PM
    Sunday, September 16, 2018 11:28 PM

All replies

  • It looks like your backup query is incomplete. It is missing some required param.

    Can you check this link where couple of approaches are suggested for db backup.


    Thanks,
    Vivek Bansod
    Blog | MSDN | LinkedIn
     

    Monday, September 17, 2018 4:44 AM
  • Hi MyCatAlex,

    Please try the following code. 

    string database = con.Database.ToString();
        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }
        try
        {
            string sqlStmt2 = string.Format("ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
            SqlCommand bu2 = new SqlCommand(sqlStmt2, con);
            bu2.ExecuteNonQuery();
    
            string sqlStmt3 = "USE MASTER RESTORE DATABASE [" + database + "] FROM DISK='ComeAndGetThr.bak' WITH REPLACE;";
            SqlCommand bu3 = new SqlCommand(sqlStmt3, con);
            bu3.ExecuteNonQuery();
    
            string sqlStmt4 = string.Format("ALTER DATABASE [" + database + "] SET MULTI_USER");
            SqlCommand bu4 = new SqlCommand(sqlStmt4, con);
            bu4.ExecuteNonQuery();
    
            MessageBox.Show("database restoration done successefully");
            con.Close();
    
       }
       catch (Exception ex)
       {
            MessageBox.Show(ex.ToString());
       }

    For more information, please refer to:

    https://csharp-tutorials1.blogspot.com/2016/03/backup-and-restore-sql-server-database.html

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. 
    Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. 
    There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet. 

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 18, 2018 8:18 AM
    Moderator
  • An alternate (and little more work but more control) is to use SMO (Sql-Server Management Objects). The following example should be all your need. SMO is installable from Nuget, as per this page.

    using Microsoft.SqlServer.Management.Common;  
    using Microsoft.SqlServer.Management.Smo;  
    
    class A {  
       public static void Main() {  
          // Connect to the local, default instance of SQL Server.   
          Server srv = new Server();  
          // Reference the AdventureWorks2012 database.   
          Database db = default(Database);  
          db = srv.Databases["AdventureWorks2012"];  
    
          // Store the current recovery model in a variable.   
          int recoverymod;  
          recoverymod = (int)db.DatabaseOptions.RecoveryModel;  
    
          // Define a Backup object variable.   
          Backup bk = new Backup();  
    
          // Specify the type of backup, the description, the name, and the database to be backed up.   
          bk.Action = BackupActionType.Database;  
          bk.BackupSetDescription = "Full backup of Adventureworks2012";  
          bk.BackupSetName = "AdventureWorks2012 Backup";  
          bk.Database = "AdventureWorks2012";  
    
          // Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.   
          BackupDeviceItem bdi = default(BackupDeviceItem);  
          bdi = new BackupDeviceItem("Test_Full_Backup1", DeviceType.File);  
    
          // Add the device to the Backup object.   
          bk.Devices.Add(bdi);  
          // Set the Incremental property to False to specify that this is a full database backup.   
          bk.Incremental = false;  
    
          // Set the expiration date.   
          System.DateTime backupdate = new System.DateTime();  
          backupdate = new System.DateTime(2006, 10, 5);  
          bk.ExpirationDate = backupdate;  
    
          // Specify that the log must be truncated after the backup is complete.   
          bk.LogTruncation = BackupTruncateLogType.Truncate;  
    
          // Run SqlBackup to perform the full database backup on the instance of SQL Server.   
          bk.SqlBackup(srv);  
    
          // Inform the user that the backup has been completed.   
          System.Console.WriteLine("Full Backup complete.");  
    
          // Remove the backup device from the Backup object.   
          bk.Devices.Remove(bdi);  
    
          // Make a change to the database, in this case, add a table called test_table.   
          Table t = default(Table);  
          t = new Table(db, "test_table");  
          Column c = default(Column);  
          c = new Column(t, "col", DataType.Int);  
          t.Columns.Add(c);  
          t.Create();  
    
          // Create another file device for the differential backup and add the Backup object.   
          BackupDeviceItem bdid = default(BackupDeviceItem);  
          bdid = new BackupDeviceItem("Test_Differential_Backup1", DeviceType.File);  
    
          // Add the device to the Backup object.   
          bk.Devices.Add(bdid);  
    
          // Set the Incremental property to True for a differential backup.   
          bk.Incremental = true;  
    
          // Run SqlBackup to perform the incremental database backup on the instance of SQL Server.   
          bk.SqlBackup(srv);  
    
          // Inform the user that the differential backup is complete.   
          System.Console.WriteLine("Differential Backup complete.");  
    
          // Remove the device from the Backup object.   
          bk.Devices.Remove(bdid);  
    
          // Delete the AdventureWorks2012 database before restoring it  
          // db.Drop();  
    
          // Define a Restore object variable.  
          Restore rs = new Restore();  
    
          // Set the NoRecovery property to true, so the transactions are not recovered.   
          rs.NoRecovery = true;  
    
          // Add the device that contains the full database backup to the Restore object.   
          rs.Devices.Add(bdi);  
    
          // Specify the database name.   
          rs.Database = "AdventureWorks2012";  
    
          // Restore the full database backup with no recovery.   
          rs.SqlRestore(srv);  
    
          // Inform the user that the Full Database Restore is complete.   
          Console.WriteLine("Full Database Restore complete.");  
    
          // reacquire a reference to the database  
          db = srv.Databases["AdventureWorks2012"];  
    
          // Remove the device from the Restore object.  
          rs.Devices.Remove(bdi);  
    
          // Set the NoRecovery property to False.   
          rs.NoRecovery = false;  
    
          // Add the device that contains the differential backup to the Restore object.   
          rs.Devices.Add(bdid);  
    
          // Restore the differential database backup with recovery.   
          rs.SqlRestore(srv);  
    
          // Inform the user that the differential database restore is complete.   
          System.Console.WriteLine("Differential Database Restore complete.");  
    
          // Remove the device.   
          rs.Devices.Remove(bdid);  
    
          // Set the database recovery mode back to its original value.  
          db.RecoveryModel = (RecoveryModel)recoverymod;  
    
          // Drop the table that was added.   
          db.Tables["test_table"].Drop();  
          db.Alter();  
    
          // Remove the backup files from the hard disk.  
          // This location is dependent on the installation of SQL Server  
          System.IO.File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\Backup\\Test_Full_Backup1");  
          System.IO.File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\Backup\\Test_Differential_Backup1");  
       }  
    }  


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 18, 2018 7:15 PM
    Moderator