如何在c#中以编程方式备份Sql数据库

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框架中使用c#编写一个代码来备份我的Sql Server 2008数据库。有人能帮忙吗?

6 个解决方案

#1

The following Link has 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. 全数据库备份
3. Differential Database Backup
4. 差异数据库备份
5. Transaction Log Backup
6. 事务日志备份
7. Backup with Compression
8. 与压缩备份

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

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

#2

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 here for examples.

在这里看到的例子。

#3

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

#4

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

#5

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

#6

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