在c#中通过SMO (SQL Server管理对象)进行数据库备份

本文关键字：对象 管理 备份 数据库 Server SQL SMO | 更新日期: 2023-09-27 18:08:32

我需要备份数据库(使用SQL Server 2008 R2)。db的大小约为100gb，所以我只想备份重要表(包含设置)的内容，当然还有所有表，视图，触发器等对象。

例如:

  • db: Products
  • tables: Food, Clothes, Cars

由于Cars中的小车太多，所以我只备份表定义(CREATE TABLE ...)，完成Food和Clothes(包括其内容)。

请告诉我最好的解决办法。我可能会使用SMO(如果没有更好的解决方案)。我应该使用Backup类吗?还是Scripter类?或者另一个(如果有的话)?哪个类可以满足我的要求?

我想把这些文件备份到*.sql文件，如果可能的话，每个表一个。

我将欣赏代码示例。写在答案或其他地方(post url)，但要确保外部文章有解决这种问题的确切方法。

你可以使用这部分代码

ServerConnection connection = new ServerConnection("SERVER,1234", "User", "User1234");
Server server = new Server(connection);
Database database = server.Databases["DbToBackup"];

在c#中通过SMO (SQL Server管理对象)进行数据库备份

使用SMO。你必须使用你需要的选项。

StringBuilder sb = new StringBuilder();
using (SqlConnection connection = new SqlConnection("connectionString")) {
    ServerConnection serverConnection = new ServerConnection(connection);
    Server server = new Server(serverConnection);
    Database database = server.Databases["databaseName"];
    Scripter scripter = new Scripter(server);
    scripter.Options.ScriptDrops = false;
    scripter.Options.WithDependencies = true;
    scripter.Options.ScriptData = true;
    Urn[] smoObjects = new Urn[1];
    foreach (Table table in database.Tables) {
        smoObjects[0] = table.Urn;
        if (!table.IsSystemObject) {
            foreach (string s in scripter.EnumScript(smoObjects)) {
                System.Diagnostics.Debug.WriteLine(s);
                sb.AppendLine(s);
            }
        }
    }
}
// Write to *.sql file on disk
File.WriteAllText(@".'backup.sql");
另一种简单的方法是将数据库备份为xml文件。要做到这一点，请使用数据表并调用WriteXml和WriteXmlSchema。(稍后需要该模式，以便可以使用相同的方法导入/恢复它)。此方法意味着您正在对每个表进行备份。
private bool BackupTable(string connectionString, string tableName, string directory) {
    using (SqlConnection connection = new SqlConnection(connectionString)) {
        try {
            connection.Open();
        }
        catch (System.Data.SqlClient.SqlException ex) {
            // Handle
            return false;
        }
        using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT * FROM {0}", tableName), connection)) {
            using (DataTable table = new DataTable(tableName)) {
                adapter.Fill(table);
                try {
                    table.WriteXml(Path.Combine(directory, string.Format("{0}.xml", tableName)));
                    table.WriteXmlSchema(Path.Combine(directory, string.Format("{0}.xsd", tableName)));
                }
                catch (System.UnauthorizedAccessException ex) {
                    // Handle
                    return false;
                }
            }
        }
    }
    return true;
}

稍后，您可以使用ReadXmlSchema和ReadXml将这些推回数据库，使用适配器填充表并将其更新到数据库中。我认为你对基本的CRUD有一定的了解，所以我不需要介绍那部分。

如果您想使用SMO，这里有一篇关于使用Backup和Restore类备份和恢复数据库的Msdn文章。代码示例是未格式化的，并且是用VB编写的。. NET，但易于翻译。

  • http://msdn.microsoft.com/en-us/library/ms162133 (v = SQL.100) . aspx

最后，这可能更容易，与IT人员交谈，看看他们是否允许您远程进入或允许您自己进行备份。如果你正在编写软件，这是至关重要的一步，那就大胆地说出来，让他们知道这对你来说有多重要，因为这将减少你在已有优秀工具的情况下编写自定义工具的成本。特别是因为数据库是100GB，所以可以使用已知的工具。

这篇文章提供的信息足以解决我的问题。这是我的工作解决方案。我决定脚本所有对象到一个文件，这是更好的解决方案，因为依赖关系，我认为。如果每个文件有一个表，并且还有一些依赖项(例如外键)，它将编写比所有内容都在一个文件中更多的代码。

在这个示例中，我省略了一些代码部分，比如在数据库备份错误时备份备份文件。如果没有这样的系统，所有的备份都将脚本化到一个文件中，这会变得很混乱 
public class DatabaseBackup
{
    private ServerConnection Connection;
    private Server Server;
    private Database Database;
    private ScriptingOptions Options;
    private string FileName;
    private const string NoDataScript = "Cars";
    public DatabaseBackup(string server, string login, string password, string database)
    {
        Connection = new ServerConnection(server, login, password);
        Server = new Server(Connection);
        Database = Server.Databases[database];
    }
    public void Backup(string fileName)
    {
        FileName = fileName;
        SetupOptions();
        foreach (Table table in Database.Tables)
        {
             if (!table.IsSystemObject)
             {
                  if (NoDataScript.Contains(table.Name))
                  {
                       Options.ScriptData = false;
                       table.EnumScript(Options);
                       Options.ScriptData = true;
                  }
                  else
                       table.EnumScript(Options);
              }
         }
    }
    private void SetupOptions()
    {
         Options = new ScriptingOptions();
         Options.ScriptSchema = true;
         Options.ScriptData = true;
         Options.ScriptDrops = false;
         Options.WithDependencies = true;
         Options.Indexes = true;
         Options.FileName = FileName;
         Options.EnforceScriptingOptions = true;
         Options.IncludeHeaders = true;
         Options.AppendToFile = true;
    }
}

Server databaseServer = default(Server);//DataBase Server Name
databaseServer = new Server("ecrisqlstddev");
string strFileName = @"C:'Images'UltimateSurveyMod_" + DateTime.Today.ToString("yyyyMMdd") + ".sql"; //20120720
if (System.IO.File.Exists(strFileName))
    System.IO.File.Delete(strFileName);
List<SqlSmoObject> list = new List<SqlSmoObject>();
Scripter scripter = new Scripter(databaseServer);
Database dbUltimateSurvey = databaseServer.Databases["UltimateSurvey"];//DataBase Name
// Table scripting Writing
DataTable dataTable1 = dbUltimateSurvey.EnumObjects(DatabaseObjectTypes.Table);
foreach (DataRow drTable in dataTable1.Rows)
{
    // string strTableSchema = (string)drTable["Schema"];
    // if (strTableSchema == "dbo")
    //    continue;
    Table dbTable = (Table)databaseServer.GetSmoObject(new Urn((string)drTable["Urn"]));
    if (!dbTable.IsSystemObject)
        if (dbTable.Name.Contains("SASTool_"))
            list.Add(dbTable);
}
scripter.Server = databaseServer;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = strFileName;
scripter.Options.DriAll = true;
scripter.Options.AppendToFile = true;
scripter.Script(list.ToArray());     // Table Script completed
// Stored procedures scripting writing
list = new List<SqlSmoObject>();
DataTable dataTable = dbUltimateSurvey.EnumObjects(DatabaseObjectTypes.StoredProcedure);
foreach (DataRow row in dataTable.Rows)
{
    string sSchema = (string)row["Schema"];
    if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
        continue;
    StoredProcedure sp = (StoredProcedure)databaseServer.GetSmoObject(
               new Urn((string)row["Urn"]));
    if (!sp.IsSystemObject)
        if (sp.Name.Contains("custom_"))
            list.Add(sp);
}
scripter.Server = databaseServer;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = strFileName;
scripter.Options.DriAll = true;
scripter.Options.AppendToFile = true;
scripter.Script(list.ToArray());    // Stored procedures script completed

你所描述的并不是真正的备份，但我明白你的目标是什么:

  • 脚本示例代码
  • 使用SMO获取表默认值的创建脚本
  • http://blogs.msdn.com/b/mwories/archive/2005/05/07/basic-scripting.aspx
  • http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scripter.aspx
  • http://weblogs.asp.net/shahar/archive/2010/03/03/generating-sql-backup-script-for-tables-amp-data-from-any-net-application-using-smo.aspx
  • http://en.csharp-online.net/SQL_Server_Management_Objects
  • http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/

对于数据的"备份"，您可以通过Reader将表内容加载到DataTable中，并将结果存储为XML…

相关文章：