How to backup Sql Database Programmatically in C

Question

I want to write a code to backup my Sql Server 2008 Database using C  in  Net 4 FrameWork  Can anyone help in this

User · Answer

You can take database back-up of SQL server instance using C#, as below

Step 1: Install Nuget package "Install-Package Microsoft.SqlServer.SqlManagementObjects"

Step 2: Use the below C# Command to take backup using Custom function

public void BackupDatabase(string databaseName, string userName, string password, string serverName, string destinationPath)

 {  
//Define a Backup object variable.
Backup sqlBackup = new Backup();
//Specify the type of backup, the description, the name, and the database to be backed up.
sqlBackup.Action = BackupActionType.Database;

sqlBackup.BackupSetDescription = "BackUp of:" + databaseName + "on" + DateTime.Now.ToShortDateString();

sqlBackup.BackupSetName = "FullBackUp";

sqlBackup.Database = databaseName;
//Declare a BackupDeviceItem
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath + "FullBackUp.bak", DeviceType.File);

//Define Server connection
ServerConnection connection = new ServerConnection(serverName, userName, password); //To Avoid TimeOut Exception
Server sqlServer = new Server(connection);
sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
Database db = sqlServer.Databases[databaseName];
(Reference Database As microsoft.sqlserver.management.smo.database, not as System.entity.database)

sqlBackup.Initialize = true;

sqlBackup.Checksum = true;

sqlBackup.ContinueAfterError = true;
//Add the device to the Backup object.
sqlBackup.Devices.Add(deviceItem);

//Set the Incremental property to False to specify that this is a full database backup. 
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);

//Specify that the log must be truncated after the backup is complete.        
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;

//Run SqlBackup to perform the full database backup on the instance of SQL Server. 
sqlBackup.SqlBackup(sqlServer);

//Remove the backup device from the Backup object.           
 sqlBackup.Devices.Remove(deviceItem);

}

Add References

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended
Microsoft.SqlServer.SqlEnum

That's it, you are done, it will take backup of specified database at specified location passed to the function.

Source: Various ways to back up SQL server database

Note: User must have proper rights to write backup data on specified disk location.

User · Answer

you can connect to the database using SqlConnection and SqlCommand and execute the following command text for example   BACKUP DATABASE  MyDatabase  TO  DISK    C       MyDatabase bak    See here for examples

User · Answer

You can use the following queries to Backup and Restore  you must change the path for your backup  Database name  data   Backup   BACKUP DATABASE  data  TO  DISK   N C  Program Files Microsoft SQL Server MSSQL10 50 MSSQLSERVER MSSQL Backup data bak  WITH NOFORMAT  NOINIT   NAME   N data-Full Database Backup   SKIP  NOREWIND  NOUNLOAD   STATS   10 GO   Restore   RESTORE DATABASE  data  FROM  DISK   N C  Program Files Microsoft SQL Server MSSQL10 50 MSSQLSERVER MSSQL Backup data bak  WITH  FILE   1   NOUNLOAD   REPLACE   STATS   10 GO

User · Answer

Works for me   public class BackupService       private readonly string  connectionString      private readonly string  backupFolderFullPath      private readonly string    systemDatabaseNames      master    tempdb    model    msdb          public BackupService string connectionString  string backupFolderFullPath                 connectionString   connectionString           backupFolderFullPath   backupFolderFullPath             public void BackupAllUserDatabases                 foreach  string databaseName in GetAllUserDatabases                          BackupDatabase databaseName                        public void BackupDatabase string databaseName                string filePath   BuildBackupPathWithFilename databaseName            using  var connection   new SqlConnection  connectionString                         var query   String Format  BACKUP DATABASE   0   TO DISK   1     databaseName  filePath                using  var command   new SqlCommand query  connection                                 connection Open                    command ExecuteNonQuery                                       private IEnumerable lt string gt  GetAllUserDatabases                 var databases   new List lt String gt              DataTable databasesTable           using  var connection   new SqlConnection  connectionString                         connection Open                 databasesTable   connection GetSchema  Databases                 connection Close                       foreach  DataRow row in databasesTable Rows                        string databaseName   row  database name   ToString                 if   systemDatabaseNames Contains databaseName                   continue               databases Add databaseName                      return databases             private string BuildBackupPathWithFilename string databaseName                string filename   string Format   0 - 1  bak   databaseName  DateTime Now ToString  yyyy-MM-dd              return Path Combine  backupFolderFullPath  filename

User · Answer

SqlConnection con   new SqlConnection                SqlCommand sqlcmd   new SqlCommand                SqlDataAdapter da   new SqlDataAdapter                DataTable dt   new DataTable                 con ConnectionString   ConfigurationManager ConnectionStrings  MyConString   ConnectionString              string backupDIR      BackupDB               string path   Server MapPath backupDIR                try                               var databaseName    MyFirstDatabase                   con Open                    string saveFileName    HiteshBackup                   sqlcmd   new SqlCommand  backup database   databaseName BKSDatabaseName    to disk      path          saveFileName     Bak    con                   sqlcmd ExecuteNonQuery                    con Close                                       ViewBag Success    Backup database successfully                   return View  Create                              catch  Exception ex                                ViewBag Error    Error Occured During DB backup process   lt br gt     ex ToString                    return View  Create

User · Answer

It s a good practice to use a config file like this    lt  xml version  1 0  encoding  utf-8   gt   lt configuration gt     lt connectionStrings gt       lt add name  MyConnString  connectionString  Data Source  local  Initial Catalog MyDB  Integrated Security SSPI   Timeout 30   gt     lt  connectionStrings gt     lt appSettings gt       lt add key  BackupFolder  value  C  temp    gt     lt  appSettings gt   lt  configuration gt     Your C  code will be something like this      read connectionstring from config file var connectionString   ConfigurationManager ConnectionStrings  MyConnString   ConnectionString       read backup folder from config file   C  temp    var backupFolder   ConfigurationManager AppSettings  BackupFolder     var sqlConStrBuilder   new SqlConnectionStringBuilder connectionString       set backupfilename  you will get something like   C  temp MyDatabase-2013-12-07 bak   var backupFileName   String Format   0  1 - 2  bak        backupFolder  sqlConStrBuilder InitialCatalog       DateTime Now ToString  yyyy-MM-dd      using  var connection   new SqlConnection sqlConStrBuilder ConnectionString         var query   String Format  BACKUP DATABASE  0  TO DISK   1              sqlConStrBuilder InitialCatalog  backupFileName        using  var command   new SqlCommand query  connection                 connection Open            command ExecuteNonQuery

User · Answer

I have new method without SMO problems 1  Create  bat File with backup sqlcmd command  for backup  SqlCmd -E -S Server Name    Q    BACKUP DATABASE  Name of Database  TO DISK    X PathToBackupLocation Name of Database  bak       for restore  SqlCmd -E -S Server Name    Q    RESTORE DATABASE  Name of Database  FROM DISK    X PathToBackupFile File Name  bak      2  Run the the bat file with WPF C  code         FileInfo file   new FileInfo  quot DB  batfile bat quot            Process process   new Process            process StartInfo FileName   file FullName          process StartInfo Arguments     quot -X quot           process StartInfo WindowStyle   ProcessWindowStyle Normal          process StartInfo UseShellExecute   false    Changed Line         process StartInfo RedirectStandardOutput   true     Changed Line         process Start            string output   process StandardOutput ReadToEnd      Changed Line         process WaitForExit      Moved Line

User · Answer

private void BackupManager Load object sender  EventArgs e                        txtFileName Text    DB Backup     DateTime Now ToString  dd-MMM-yy                       private void btnDBBackup Click object sender  EventArgs e                        if   string IsNullOrEmpty txtFileName Text Trim                                   BackUp                              else                               MessageBox Show  Please Enter Backup File Name       MessageBoxButtons OK  MessageBoxIcon Information                   txtFileName Focus                    return                                   private void BackUp                         try                                progressBar1 Value   0                   for  progressBar1 Value   0  progressBar1 Value  lt  100  progressBar1 Value                                                          pl DbName    Inventry                   pl Path     D      txtFileName Text Trim       bak                    for  progressBar1 Value   100  progressBar1 Value  lt  200  progressBar1 Value                                                          bl DbBackUp pl                   for  progressBar1 Value   200  progressBar1 Value  lt  300  progressBar1 Value                                                          for  progressBar1 Value   300  progressBar1 Value  lt  400  progressBar1 Value                                                          for  progressBar1 Value   400  progressBar1 Value  lt  progressBar1 Maximum  progressBar1 Value                                                         if  progressBar1 Value    progressBar1 Maximum                                        MessageBox Show  Backup Saved Successfully             MessageBoxButtons OK  MessageBoxIcon Information                                     else                                       MessageBox Show  Action Failed  Please try again later       MessageBoxButtons OK  MessageBoxIcon Error                                                catch  Exception ex                                MessageBox Show  Action Failed  Please try again later       MessageBoxButtons OK  MessageBoxIcon Error                             finally                               progressBar1 Value   0

User · Answer

The following Link has explained complete details about how to back sql server 2008 database using c   Sql Database backup can be done using many way  You can either use Sql Commands like in the other answer or have create your own class to backup data   But these are different mode of backup    Full Database Backup Differential Database Backup Transaction Log Backup Backup with Compression   But the disadvantage with this method is that it needs your sql management studio to be installed on your client system

User · Answer

This worked for me     private void BackupButtonClick object sender  RoutedEventArgs e           FILE NAME WITH DATE DISTICNTION     string fileName   string Format  SchoolBackup  0  bak   DateTime Now ToString  yyyy MM dd h mm tt         try                  YOUR SEREVER OR MACHINE NAME         Server dbServer   new Server  new ServerConnection  DESKTOP             Microsoft SqlServer Management Smo Backup dbBackup   new Microsoft SqlServer Management Smo Backup                         Action   BackupActionType Database               Database    School                      dbBackup Devices AddDevice  backupDirectory          fileName  DeviceType File           dbBackup Initialize   true          dbBackup SqlBackupAsync dbServer             MessageBox Show  Backup    Backup Completed               catch Exception err                System Windows MessageBox Show err ToString                  THE DIRECTOTRY YOU WANT TO SAVE IN public string backupDirectory         using  var dialog   new FolderBrowserDialog                  var result   dialog ShowDialog            return dialog SelectedPath

[.net] How to backup Sql Database Programmatically in C#

Examples related to .net

Examples related to database

Examples related to sql-server-2008

Examples related to c#-4.0

Examples related to backup