SQL Server 管理对象

核心提示： 目录 SMO 基础知识列举服务器连接到服务器列举数据库发出 DDL 或 DML 命令遍历对象列表备份数据库恢复数据库验证数据库备份结束语数据库开发人员不仅必须查询和处理数据，还必须定期执行管理任务，SQL Server 管理对象，SQL Server™ 管理对象 (SMO) 为开发人员提供了强大的

目录

SMO 基础知识

列举服务器

连接到服务器

列举数据库

发出 DDL 或 DML 命令

遍历对象列表

备份数据库

恢复数据库

验证数据库备份

结束语

数据库开发人员不仅必须查询和处理数据，还必须定期执行管理任务。SQL Server™ 管理对象 (SMO) 为开发人员提供了强大的工具集，用于备份和恢复数据库，以及发出数据定义语言 (DDL) 命令等操作。通过使用 SQL SMO，您还可以连接到 SQL Server，遍历数据库对象的集合，并对其执行各种任务。

在本期专栏中，我将介绍如何使用 SMO 来检查数据库对象并执行各种管理任务。具体而言，我将介绍如何设计您的项目以使用 SMO 以及如何连接到服务器等主题。我还将介绍一个对数据库发出 DDL 或数据操作语言 (DML) 命令的示例应用程序。最后，我将演示如何使用 SMO 来执行数据库备份、恢复和备份验证。

SMO 基础知识

SMO 是一种 Microsoft® .NET Framework 对象库，可让您管理一个或多个数据库服务器。其目的在于使用 SQL Server 2005 引入的新功能，但它还将连接到支持各自旧版功能的 SQL Server 2000 数据库。但是，部分 SMO 的类、属性和方法只能与 SQL Server 2005 配合使用才能支持诸如 SQL Service Broker 和快照隔离之类的新功能。

虽然基于 COM 的 SQL 分布式管理对象 (SQL-DMO) 仍附带有 SQL Server 2005，但是它未经更新，不能像 SMO 一样可直接访问最新的 SQL Server 2005 功能。设计管理 SQL Server 数据库的 .NET 应用程序时，建议您优先选择 SQL SMO，而不推荐使用具有 DMO 的 COM interop。您还应当考虑升级目前使用 DMO 的应用程序，以转而使用 SMO。

您可在安装 Microsoft® SQL Server 2005 时勾选“安装客户端工具”选项来安装 SMO。然后在创建即将使用 SMO 的项目时，应首先引用适当的程序集。您必须引用的两个主要 DLL 是 Microsoft.SqlServer.Smo.dll 和 Microsoft.SqlServer.ConnectionInfo.dll。前者包含核心 SMO 类（如数据库、服务器、备份和恢复），而后者包含有助于管理与 SQL Server 对象之间的连接的类库。在许多情况下，您还需要引用 Microsoft.SqlServer.SqlEnum.dll 和 Microsoft.SqlServer.SmoEnum.dll，其中前者主要包含有用的枚举；后者提供用于以编程方式访问统一资源名称 (URN) 地址的 Urn 类，URN 地址用于唯一标识 Microsoft SQL Server 对象。

列举服务器

您可使用 SMO 来检索网络上安装的 SQL Server 列表。您可指定参数，以控制是在网络上还是仅在本地搜索 SQL Server。SmoApplication 类提供了一种名为 EnumAvailableSqlServers 的静态方法。如果传递的 Boolean 值为 true，该方法将仅搜索和返回所有本地服务器。如果向本方法传递的值为 false，则该方法将搜索网络上的所有可用服务器。以下代码将获取网络上 SQL Server 的实例列表，并将其返回至 ADO.NET DataTable：

DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow row in dtServers.Rows)
{
　string sqlServerName = row[“Server”].ToString();
　if (row[“Instance”] != null && row[“Instance”].ToString().Length > 0)
　　　sqlServerName += @”” + row[“Instance”].ToString();
}

它随后将遍历 DataTable 各行并格式化 SQL Server 及其实例（如果存在实例）的名称。遗憾的是，本方法不大可靠，原因是它依赖于结合了超时的 UDP 广播，因此可能会得到不一致或不完整的结果。

连接到服务器

以下几个示例均为《MSDN® 杂志》网站上可供下载或浏览的应用程序的一部分。当使用 SMO 构建应用程序时，首先执行的步骤之一通常是连接到 SQL Server 实例。这里没什么特别之处；建立连接需要您传递 SQL Server 的名称（如果存在多个实例，还需要其实例名称）以及登录凭据。如果想使用 Windows® 身份验证建立连接，您可使用 SMO 指定这种方式。但如果想使用 SQL 身份验证建立连接，则必须传递有效的登录名和密码。

图 1 显示了一个示例应用程序，其中用户可输入 SQL Server 实例名称和他的凭据。在本图中，我输入了一个实例化的 SQL Server 的名称，并告诉它使用 Windows 身份验证连接到服务器。当我单击“连接”按钮时，便会填充数据库列表。

图 1通过 SMO 连接到服务器

我在示例应用程序中创建一个名为 SMOHelper 的类，以协助提供我的应用程序将用到的一些通用例程。SMOHelper 类的实例可用于和单个 SQL Server 交互。该实例拥有一个构造函数，负责接受通过 Windows 或 SQL Server 身份验证连接到服务器所需的信息。图 2 显示了负责获取和设置适当类属性的 SMOHelper 构造函数，此类的方法将使用类属性来执行数据库操作。

Figure2接受服务器连接信息

public SMOHelper(string serverAndInstanceName,
　　　string userName, string password,
　　　bool useWindowsAuthentication)
{
　this.server = new Server();
　int slashPos = serverAndInstanceName.IndexOf(‘’);
　if (slashPos >= 0)
　{
　　this.serverName = serverAndInstanceName.Substring(0, slashPos);
　　this.instance = serverAndInstanceName.Substring(slashPos + 1);
　}
　else
　{
　　this.serverName = serverAndInstanceName;
　　this.instance = string.Empty;
　}
　this.userName = userName;
　this.password = password;
　this.useWindowsAuthentication = useWindowsAuthentication;
}

通过创建 Server 类的实例并设置其 ConnectionContext 类的属性来建立与服务器的连接。图 3 所示代码根据用户在窗体中选择的身份验证类型来设置连接。当您可以显式调用 ConnectionContext 的 Connect 方法时，这实际上并不是必需的，因为 SMO 会自动从池中调用和释放连接。有关详细信息，请参见 blogs.msdn.com/mwories/archive/2005/05/02/smoconnections.aspx。

Figure3连接到服务器

public void Connect()
{
　　server.ConnectionContext.ServerInstance = ServerAndInstanceName;
　　server.ConnectionContext.LoginSecure = useWindowsAuthentication;
　　if (!this.useWindowsAuthentication)
　　{
　　　　server.ConnectionContext.Login = this.userName;
　　　　server.ConnectionContext.Password = this.password;
　　}
　　// server.ConnectionContext.Connect();
}

列举数据库

一旦连接到服务器，可通过 Server 对象的 Databases 属性检索该服务器上的数据库列表。在我的示例应用程序中，我使用了数据绑定，将此服务器上的数据库列表绑定到一个组合框控件上。我遍历了 DatabaseCollection 并创建了一个 List<string> 来包含这些数据库的名称。随后将本列表绑定到组合框控件中，用户可以在此选择其中一个数据库并对其执行操作。以下代码段会遍历数据库并生成名称字符串的列表。

public List<string> GetDatabaseNameList()
{
　　List<string> dbList = new List<string>();
　　foreach (Database db in server.Databases)
　　　　dbList.Add(db.Name);
　　return dbList;
}

在以上显示的代码段中，我只想得到一个数据库名称的列表，因此我只创建了一个 List<string>。但也可同样轻易地将组合框控件与自定义类的列表（如 List<MyDatabase>）绑定在一起，该列表包含通常由许多 SMO 方法返回的多功能对象。

发出 DDL 或 DML 命令

SMO 与 ADO.NET 类似，可用于在数据库上执行命令，使用标准 DML 检索或修改数据。尽管 SMO 未得到优化，不可处理复杂的 DML，但是仍可用于执行简单的 DML 语句。

图 1 中的窗体显示了一个文本框，用户可在其中输入 DML 或 DDL 语句并执行该语句。如果查询应检索行集合（即 SELECT 语句），SMO 将通过其 ExecuteWithResults 方法检索此行集合。否则，如果命令不返回行集合（INSERT、UPDATE 或 DELETE），SMO 将使用其 ExecuteNonQuery 方法执行命令。以下代码示例执行了一次查询并将行集合返回至 ADO.NET DataSet 中。DataSet 随后被绑定到窗体中的 DataGridView：

　DataSet ds = db.ExecuteWithResults(txtSQL.Text);
　dgvResults.DataSource = ds.Tables[0];

下一个代码段将对数据库对象执行一条无返回结果的命令。

　db.ExecuteNonQuery(sql);

您使用的命令不必是 DML 命令，也可以是 DDL 命令。例如，您可使用 ExecuteNonQuery 方法执行 ALTER TABLE、CREATE PROCEDURE、RESTORE，甚至 DBCC CHECKDBB 命令。例如，可使用以下命令来创建 Person 表：

　　string sql = “CREATE TABLE Person “ +
　　　 “(PersonID INT NOT NULL PRIMARY KEY,
　　　 FullName VARCHAR(100))”;
　　db.ExecuteNonQuery(sql);

遍历对象列表

SMO 还可用于找到特定的数据库对象并对其执行与上下文相关的操作。SMO 的 Database 类提供了其包含的每个对象的一系列集合。图 4 显示了 Database 类提供的不同集合的部分列表。

Figure4集合

默认值

FileGroups（文件组）

角色

规则

架构

StoredProcedures（存储过程）

表

触发器

UserDefinedFunctions（用户定义函数）

用户

视图

图 5 显示了一个示例应用程序，该应用程序显示了本人开发计算机上 Northwind 数据库内的所有表、存储过程和用户。获取特定数据库内存在的表列表非常容易。一旦拥有 Server 对象并从 Server 对象中获得所需的 Database 对象，您就可以通过 Tables 属性访问 TablesCollection。图 6 显示了如何访问 Database 的对象集合、遍历这些集合并构建您自己的自定义对象。在本代码中，我只是获取了对象的名称并将其放入 List<string>，随后便用它绑定到 SmoLists 窗体中的组合框控件上（如图 5 所示）。

Figure6检索列表

public List<string> GetTableNameList(Database db)
{
　　List<string> tableList = new List<string>();
　　foreach (Table table in db.Tables) tableList.Add(table.Name);
　　return tableList;
}
public List<string> GetStoredProcedureNameList(Database db)
{
　　List<string> storedProcedureNameList = new List<string>();
　　foreach (StoredProcedure storedProcedure in db.StoredProcedures)
　　　　storedProcedureNameList.Add(storedProcedure.Name);
　　return storedProcedureNameList;
}
public List<string> GetUserNameList(Database db)
{
　　List<string> userNameList = new List<string>();
　　foreach (User user in db.Users) userNameList.Add(user.Name);
　　return userNameList;
}

图 5数据库列表

当然，除 Name 属性外，这些对象中还包含许多其他属性和方法。例如，您还可以通过对 Table 对象执行 Revoke 或 Grant 之类的方法来设置 Table 的权限。

备份数据库

开发人员使用 SMO 完成的更常见任务之一是备份数据库。SMO 通过使用其 Backup 类，相对简化了数据库备份的执行。图 7 显示了将允许您备份数据库并随后进行恢复的示例窗体。

图 7备份和恢复

当运行应用程序并单击“备份”按钮时，您将看到进度条。我在窗体中添加了一个 ProgressBar 控件，以便为用户提供一些有关备份或恢复操作状态的反馈。SMO Backup 类提供了一些可用于帮助跟踪备份进度的事件。（我将立即对其进行检查。）

当用户单击“备份”按钮时，随即执行 btnBackup_Click 事件处理程序，如以下代码所示：

private void btnBackup_Click(
　 object sender, EventArgs e)
{
　 pbDBAction.Maximum = 100;
　 pbDBAction.Style = ProgressBarStyle.Blocks;
　 pbDBAction.Value = 0;
　 pbDBAction.Step = 10;
　 string databaseName = cboDatabase.SelectedValue.ToString();
　 smoHelper.BackupDatabase(databaseName);
}

首先，我将设置 Progressbar 控件属性，以便它以程序块集的形式出现，并以空白开始。接下来，我将从组合框控件的选项中获取数据库的名称并将其传递给我事先在图 8 中为 SMOHelper 类创建的 BackupDatabase 方法中。

Figure8备份数据库

public void BackupDatabase(string databaseName)
{
　　Console.WriteLine(“*** Backing up ***”);
　　Backup backup = new Backup();
　　backup.Action = BackupActionType.Database;
　　backup.Database = databaseName;
　　backup.Incremental = false;
　　backup.Initialize = true;
　　backup.LogTruncation = BackupTruncateLogType.Truncate;
　　string fileName = string.Format(“{0}{1}.bak”,
　　　testFolder, databaseName);
　　BackupDeviceItem backupItemDevice =
　　　new BackupDeviceItem(fileName, DeviceType.File);
　　backup.Devices.Add(backupItemDevice);
　　backup.PercentCompleteNotification = 10;
　　backup.PercentComplete += backup_PercentComplete;
　　backup.Complete += backup_Complete;
　　backup.SqlBackup(server);
}

在 BackupDatabase 中，我首先创建一个 Backup 类的实例。然后我将 Action 属性设置为 BackupActionType.Database 枚举值（您还可以备份日志和文件）。当然，我然后将数据库名称设置为备份，并设置了一些其他属性。因为我想进行完整的数据库备份，因此我通过将 Incremental 属性设置为 false 来指示备份不进行增量式备份。

在备份数据库时，您必须指定想在其中创建备份文件的文件路径位置和文件名。在我的代码中，为了简明起见，我将所有备份都放在同一个文件夹内（请参见 testFolder 变量）。选择了创建备份文件的位置后，我创建了一个 BackupDeviceItem 并将其添加到 Backup 类的 Devices 列表中。我选择将备份保存为文件，但您也可将备份保存到 LogicalDevice 或磁带之类的其他目的地。

我为 PercentComplete 事件添加了一个事件处理程序，这样备份过程就可以通知调用窗体及其 ProgressBar 控件。该事件按 PercentCompleteNotification 属性定义的间隔发生。在我的示例应用程序中，就是本方法使 ProgressBar 控件显示进度的。此外，我也为 Complete 事件添加了事件处理程序，以便完成备份时可以通知窗体（有关如何将事件联系一起的详细信息，请参见示例应用程序）。

最后，我发出 SqlBackup 方法，正是它在实际执行数据库备份任务。请记住，备份数据库的过程可能比预期要花更长的时间，这取决于许多因素，包括数据库大小及其内容。

恢复数据库

SMO 提供了一个 Restore 类，该类可用于从文件或其他备份设备中恢复数据库。在示例应用程序中，我将恢复刚刚备份好的文件，过程差不多。请注意，我并不将文件恢复至备份该文件的数据库，而是用新的文件名和新的逻辑名称来恢复此数据库。实际上，这是使用备份方法创建一个数据库副本。当然，您也可以选择在现有数据库的基础上恢复该数据库，这一过程非常方便。

图 9 所示代码显示了我在示例应用程序中恢复数据库所使用的 RestoreDB 方法。我的第一步是创建一个 Restore 类的实例，然后设置将要恢复的备份文件的名称。

Figure9恢复数据库

public void RestoreDB(string databaseName)
{
　　Restore restore = new Restore();
　　string fileName = string.Format(“{0}{1}.bak”,
　　　　testFolder, databaseName);
　　restore.Devices.Add(new BackupDeviceItem(fileName, DeviceType.File));
　　// Just give it a new name
　　string destinationDatabaseName =
　　　　string.Format(“{0}_newly_restored”, databaseName);
　　// Go grab the current database’s logical names for the data
　　// and log files. For this example, we assume there are 1 for each.
　　Database currentDatabase = server.Databases[databaseName];
　　string currentLogicalData =
　　　　currentDatabase.FileGroups[0].Files[0].Name;
　　string currentLogicalLog = currentDatabase.LogFiles[0].Name;
　　// Now relocate the data and log files
　　RelocateFile reloData =
　　　　new RelocateFile(currentLogicalData,
　　　　string.Format(@”{0}{1}.mdf”, testFolder,
　　　　　　destinationDatabaseName));
　　RelocateFile reloLog =
　　　　new RelocateFile(currentLogicalLog,
　　　　string.Format(@”{0}{1}_Log.ldf”, testFolder,
　　　　　　destinationDatabaseName));
　　restore.RelocateFiles.Add(reloData);
　　restore.RelocateFiles.Add(reloLog);
　　restore.Database = destinationDatabaseName;
　　restore.ReplaceDatabase = true;
　　restore.PercentCompleteNotification = 10;
　　restore.PercentComplete += restore_PercentComplete;
　　restore.Complete += restore_Complete;
　　restore.SqlRestore(server);
}

第二步，我为将要恢复的数据库创建一个新的名称 (Northwind_newly_restored)，然后从 FileGroups 和 Files 集合中获取逻辑数据和日志文件的名称。我需要用这些名称来将备份的物理文件重新定位至新的名称（因为我要将此数据库恢复为独立的新数据库）。随后我将一个 RelocateFile 类的实例（重新定位的每个文件都有一个）添加到恢复对象的 RelocateFiles 集合。

我设置数据库的名称以进行恢复，并指示 SMO，如果已经存在同名的数据库，则替换现有的数据库。因为我希望 ProgressBar 显示恢复操作的进度，因此相应设置了 PercentComplete 和 Complete 事件处理程序。

验证数据库备份

如果在数据库已经损坏的情况下进行备份，并无太大好处。因此验证备份是一个好主意，且验证方法很简单，只需要创建一个 Restore 类的实例，将 DeviceType 设置为相应的介质，添加设备，然后执行 SqlVerify 方法即可。它将返回一个 Boolean 值，指示备份是否通过验证。以下代码演示了如何执行此验证：

Restore restore = new Restore();
restore.DeviceType = DeviceType.File;
restore.Devices.Add(@”c:MySMOTest.bak”);
boolean verified = restore.SqlVerify(server,out msg);

结束语

SMO 使在.NET 代码中执行数据库管理操作变得简单。由于可用 T-SQL 直接在数据库上执行所有这些任务，或者通过 SQL Server Management Studio 执行所有这些任务，因此能够使用 SMO 便意味着可以非常灵活地在 .NET 应用程序中包括这些类型的操作。事实上，SQL Server Management Studio 依赖 SMO 执行其大部分管理任务。

请将您想向 John 询问的问题和提出的意见发送至 mmdata@microsoft.com.

John Papa是一名使用 Microsoft .NET 技术的专业顾问、培训师和导师。作为一名 Microsoft C# MVP，他撰写过多篇有关体系结构和数据访问技术方面的文章和书籍，这些技术包括 ADO.NET、XML 和 SQL Server。他还经常在 VSLive 之类的行业会议上发表演讲。如果要与 John 联系，请访问 www.johnpapa.net。