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.

score 41 · Answer 1 · edited Mar 15 '17 at 14:54

41

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.

edited Mar 15 '17 at 14:54

Stacked

6,892
7
57
73

answered Mar 23 '12 at 07:38

Davide Piras

43,984
10
98
147

score 34 · Answer 2 · edited Mar 12 '23 at 03:01

34

It's a good practice to use a config file like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="MyConnString" connectionString="Data Source=(local);Initial Catalog=MyDB; Integrated Security=SSPI" ;Timeout=30"/>
  </connectionStrings>
  <appSettings>
    <add key="BackupFolder" value="C:/temp/"/>
  </appSettings>
</configuration>

Your C# code will be something like this (note the parameterization of the SQL):

// 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"));

const string query = @"
BACKUP DATABASE @db
TO DISK = @file;
";
using (var connection = new SqlConnection(sqlConStrBuilder.ConnectionString))
using (var command = new SqlCommand(query, connection))
{
    command.Parameters.Add("@db", SqlDbType.NVarChar, 128).Value = sqlConStrBuilder.InitialCatalog;
    command.Parameters.Add("@file", SqlDbType.NVarChar, 255).Value = backupFileName;

    connection.Open();
    command.ExecuteNonQuery();
}

edited Mar 12 '23 at 03:01

Charlieface

52,284
6
19
43

answered Dec 07 '13 at 00:42

Stacked

6,892
7
57
73

1

I might be wrong here, but doesn't that imply that the C# program must run on the same server as the database? – Eric Wu Jul 21 '16 at 18:32
2

@Eric Wu - The connection string in the config file can point to any SQL server, it doesn't have to be on the same machine. – michaelmsm89 Jul 21 '16 at 19:22
True. I thought it was manipulating the file in some way at application-level. Seems like _backupfolder_ refers to a path in the database server. – Eric Wu Jul 21 '16 at 20:24
how to close the backup file here.. If I try to delete .bak file after zipping it, it says " File being used by another process"... – Raveendra M Pai Apr 03 '17 at 13:07
Is anyone else experiencing "Operating system error 5(Access is denied.)" here? – simaglei Aug 06 '20 at 19:14
1

@EricWu - The bak-file will be generated on the sql-server, not where the code runs if that's what you meant... – simaglei Aug 06 '20 at 19:22

score 26 · Answer 3 · answered Mar 13 '15 at 11:55

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<string> GetAllUserDatabases()
    {
        var databases = new List<String>();

        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);
    }
}

Three years pass, but it still very perfect. Thank you so much. — Fox Vĩnh Tâm, Dec 04 '18 at 07:57
Hi, friend, can it work if I just want to back up a single table? — user618520, Feb 03 '20 at 05:58

score 17 · Accepted Answer · edited Mar 15 '17 at 14:53

17

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.

edited Mar 15 '17 at 14:53

Stacked

6,892
7
57
73

answered Mar 23 '12 at 07:39

Kishore Kumar

12,675
27
97
154

5

SMO implies SQL management objects installed locally on the machine where the .NET backup program executes, pure SQL solution does not. – Davide Piras Mar 23 '12 at 07:41
2

At some point, i needed to work with SMO, i just gave up, none of the DLL file within the all sql folders (you may find different version of same DLL within different folders) could match, even NuGet version do not provide matched version of DLL files – Hassan Faghihi Jul 28 '18 at 06:23

score 5 · Answer 5 · answered Sep 20 '16 at 05:23

            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 !<br>" + ex.ToString();
                return View("Create");
            }

score 4 · Answer 6 · edited Jun 20 '20 at 09:12

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("DB\\batfile.bat");
        Process process = new Process();
        process.StartInfo.FileName = file.FullName;
        process.StartInfo.Arguments = @"-X";
        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

thank you ... but what if the server contains username and password? — ABDULLAH MAKKI, Jun 09 '20 at 12:32

score 1 · Answer 7 · answered Sep 05 '18 at 11:17

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;
    }
}

score 0 · Answer 8 · edited Aug 13 '16 at 14:10

0

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

edited Aug 13 '16 at 14:10

Martin Tournoij

26,737
24
105
146

answered Aug 13 '16 at 13:48

Programmer

57
7

4

How is this answer different from [this answer](http://stackoverflow.com/a/9835597/660921) which has been on the fire for four years? – Martin Tournoij Aug 13 '16 at 14:11

score 0 · Answer 9 · answered Sep 02 '19 at 07:02

 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 < 100; progressBar1.Value++)
                {

                }

                pl.DbName = "Inventry";
                pl.Path = @"D:/" + txtFileName.Text.Trim() + ".bak";

                for (progressBar1.Value = 100; progressBar1.Value < 200; progressBar1.Value++)
                {

                }

                bl.DbBackUp(pl);
                for (progressBar1.Value = 200; progressBar1.Value < 300; progressBar1.Value++)
                {

                }

                for (progressBar1.Value = 300; progressBar1.Value < 400; progressBar1.Value++)
                {

                }

                for (progressBar1.Value = 400; progressBar1.Value < 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;
            }
        }

score 0 · Answer 10 · answered Jun 08 '20 at 08:48

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.

How to backup Sql Database Programmatically in C#

10 Answers10

I have new method without SMO problems

Linked

Related