C# 使用 SMO 复制数据库和数据

Using SMO to copy a database and data

提问by Rune Grimstad

I am trying to make a copy of a database to a new database on the same server. The server is my local computer running SQL 2008 Express under Windows XP. Doing this should be quite easy using the SMO.Transfer class and it almost works!

我正在尝试将数据库的副本复制到同一服务器上的新数据库。服务器是我在 Windows XP 下运行 SQL 2008 Express 的本地计算机。使用 SMO.Transfer 类应该很容易做到这一点，它几乎可以工作！

My code is as follows (somewhat simplified):

我的代码如下（有些简化）：

Server server = new Server("server");
Database sourceDatabase = server.Databases["source database"];

Database newDatbase = new Database(server, "new name");
newDatbase.Create();

Transfer transfer = new Transfer(sourceDatabase);
transfer.CopyAllObjects = true;
transfer.Options.WithDependencies = true;
transfer.DestinationDatabase = newDatbase.Name;
transfer.CopySchema = true;
transfer.CopyData = true;
StringCollection transferScript = transfer.ScriptTransfer();

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (SqlCommand switchDatabase = new SqlCommand("USE " + newDatbase.Name, conn))
    {
        switchDatabase.ExecuteNonQuery();
    }

    foreach (string scriptLine in transferScript)
    {
        using (SqlCommand scriptCmd = new SqlCommand(scriptLine, conn, transaction))
        {
            int res = scriptCmd.ExecuteNonQuery();
        }
    }
}

What I do here is to first create a new database, then generate a copy script using the Transferclass and finally running the script in the new database.

我在这里做的是首先创建一个新数据库，然后使用Transfer该类生成一个复制脚本，最后在新数据库中运行该脚本。

This works fine for copying the structure, but the CopyDataoption doesn't work!

这适用于复制结构，但该CopyData选项不起作用！

Are there any undocumented limits to the CopyDataoption? The documentation only says that the option specifies whether data is copied.

该CopyData选项是否有任何未记录的限制？文档只说该选项指定是否复制数据。

I tried using the TransferData()method to copy the databse without using a script but then I get an exception that says "Failed to connect to server" with an inner exception that says "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

我尝试使用该TransferData()方法在不使用脚本的情况下复制数据库，但随后我收到一个异常，显示“无法连接到服务器”，内部异常显示“在建立与SQL Server。未找到或无法访问服务器。请验证实例名称是否正确，以及 SQL Server 是否配置为允许远程连接。（提供程序：命名管道提供程序，错误：40 - 无法打开与 SQL Server 的连接)”

I also tried to enable Named Pipes on the server, but that doesn't help.

我还尝试在服务器上启用命名管道，但这无济于事。

Edit: I found a solution that works by making a backup and then restoring it to a new database. It's quite clumsy though, and slower than it should be, so I'm still looking for a better solution.

编辑：我找到了一个解决方案，该解决方案通过进行备份然后将其恢复到新数据库来工作。虽然它很笨拙，而且比应该的要慢，所以我仍在寻找更好的解决方案。

采纳答案by Rune Grimstad

Well, after contacting Microsft Support I got it working properly, but it is slow and more or less useless. Doing a backup and then a restore is much faster and I will be using it as long as the new copy should live on the same server as the original.

好吧，在联系 Microsft 支持后，我让它正常工作，但它很慢，或多或少没用。进行备份然后恢复要快得多，只要新副本应该与原始副本存在于同一台服务器上，我就会使用它。

The working code is as follows:

工作代码如下：

ServerConnection conn = new ServerConnection("rune\sql2008");
Server server = new Server(conn);

Database newdb = new Database(server, "new database");
newdb.Create();

Transfer transfer = new Transfer(server.Databases["source database"]);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationDatabase = newdb.Name;
transfer.DestinationServer = server.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();

The trick was to set the DestinationDatabase property. This must be set even if the target is that same as the source. In addition I had to connect to the server as a named instance instead of using the other connection options.

诀窍是设置 DestinationDatabase 属性。即使目标与源相同，也必须设置此项。此外，我必须作为命名实例连接到服务器，而不是使用其他连接选项。

回答by Rob

Try setting SetDefaultInitFieldsto true on the Serverobject.

尝试在Server对象上将SetDefaultInitFields设置为 true 。

I had the same issue with the SMO database object running slowly. I guess this is because sql server doesn't like to retrieve entire objects and collections at once, instead lazy loading everything, causing a round-trip for each field, which for an entire database is pretty inefficient.

我在 SMO 数据库对象运行缓慢时遇到了同样的问题。我猜这是因为 sql server 不喜欢一次检索整个对象和集合，而是延迟加载所有内容，导致每个字段的往返，这对于整个数据库来说效率很低。

回答by emy

I had a go at getting this working and have come up with an answer that doesn't use the Transfer class. Here is the Method i used:

我尝试了这个工作，并提出了一个不使用 Transfer 类的答案。这是我使用的方法：

       public bool CreateScript(string oldDatabase, string newDatabase)
   {
       SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=" + newDatabase + ";User Id=sa;Password=sa;");
       try
       {
           Server sv = new Server();
           Database db = sv.Databases[oldDatabase];

           Database newDatbase = new Database(sv, newDatabase);
           newDatbase.Create(); 

           ScriptingOptions options = new ScriptingOptions();
           StringBuilder sb = new StringBuilder();
           options.ScriptData = true;
           options.ScriptDrops = false;
           options.ScriptSchema = true;
           options.EnforceScriptingOptions = true;
           options.Indexes = true;
           options.IncludeHeaders = true;
           options.WithDependencies = true;

           TableCollection tables = db.Tables;

           conn.Open();
           foreach (Table mytable in tables)
           {
               foreach (string line in db.Tables[mytable.Name].EnumScript(options))
               {
                   sb.Append(line + "\r\n");
               }
           }
           string[] splitter = new string[] { "\r\nGO\r\n" };
           string[] commandTexts = sb.ToString().Split(splitter, StringSplitOptions.RemoveEmptyEntries);
           foreach (string command in commandTexts)
           {
               SqlCommand comm = new SqlCommand(command, conn);
               comm.ExecuteNonQuery();
           }
           return true;
       }
       catch (Exception e)
       {
           System.Diagnostics.Debug.WriteLine("PROGRAM FAILED: " + e.Message);
           return false;
       }
       finally
       {
           conn.Close();
       }
   }

回答by emcuapharaon

Here is my solution:

这是我的解决方案：

1. I have a Database named is Olddatabase

2. I backup it to E:\databackup\Old.bak

3. If you want to create a Duplicate Database from Olddatabase in the same server with name NewDatabase

1. 我有一个名为 Olddatabase 的数据库

2. 我备份到 E:\databackup\Old.bak

3. 如果您想在同一台服务器上从 Olddatabase 创建一个名为 NewDatabase 的重复数据库

3.1 You can use command in query tool : EXEC OldDatabase.dbo.sp_helpfile; to determinat path of OldDatabase is stored in case you want to save NewDatabase in the same folder.

3.1 查询工具中可以使用命令：EXEC OldDatabase.dbo.sp_helpfile；确定存储 OldDatabase 的路径，以防您想将 NewDatabase 保存在同一文件夹中。

or You can save NewDatabase in new Path which you want

或者您可以将 NewDatabase 保存在您想要的新路径中

1. use this command in Query tool

   RESTORE DATABASE NewDatabase FROM DISK = 'E:\databackup\Old.bak' WITH MOVE 'OldDatabase' TO 'E:\New path (or the same path)\NewDatabase_Data.mdf', MOVE 'OldDatabase_log' TO 'E:\New path (or the same path)\NewDatabase_Log.ldf';

1. 在查询工具中使用此命令

   RESTORE DATABASE NewDatabase FROM DISK = 'E:\databackup\Old.bak' WITH MOVE 'OldDatabase' TO 'E:\New path (or the same path)\NewDatabase_Data.mdf', MOVE 'OldDatabase_log' TO 'E:\New路径（或相同路径）\NewDatabase_Log.ldf';

Note: you can Use these command obove in c# with : Create a Store procedure in sql which include Above commands. And you can call the store procedure in C #

注意：您可以在 c# 中使用这些命令 obove 和：在包含上述命令的 sql 中创建存储过程。你可以在 C# 中调用存储过程