.net 如何在 C# 中以编程方式备份​​ Sql 数据库

How to backup Sql Database Programmatically in C#

提问by

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

我想编写一个代码来使用 .Net 4 FrameWork 中的 C# 备份我的 Sql Server 2008 数据库。任何人都可以帮忙。

采纳答案by Kishore Kumar

The following Linkhas explained complete details about how to back sql server 2008 database using c#

以下链接解释了有关如何使用 c# 备份 sql server 2008 数据库的完整详细信息

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.

Sql 数据库备份可以通过多种方式完成。您可以像在其他答案中一样使用 Sql 命令，也可以创建自己的类来备份数据。

But these are different mode of backup.

但这些是不同的备份模式。

1. Full Database Backup
2. Differential Database Backup
3. Transaction Log Backup
4. Backup with Compression

1. 完整数据库备份
2. 差异数据库备份
3. 事务日志备份
4. 压缩备份

But the disadvantage with this method is that it needs your sql management studio to be installed on your client system.

但是这种方法的缺点是它需要在您的客户端系统上安装您的 sql management studio。

回答by Davide Piras

you can connect to the database using SqlConnection and SqlCommand and execute the following command text for example:

您可以使用 SqlConnection 和 SqlCommand 连接到数据库并执行以下命令文本，例如：

BACKUP DATABASE [MyDatabase] TO  DISK = 'C:\....\MyDatabase.bak'

See herefor examples.

有关示例，请参见此处。

回答by Stacked

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:

您的 C# 代码将是这样的：

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

回答by Georgy Batalov

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

回答by Hitesh Patel

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

回答by Prashant Manjule

I have new method without SMO problems

我有没有 SMO 问题的新方法

1. Create .bat File with backup sqlcmd command

1.使用备份sqlcmd命令创建.bat文件

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

2.用WPF/C#代码运行bat文件

        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

回答by Farhan

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

回答by JIYAUL MUSTAPHA

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

回答by Hamed

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