如何以编程方式克隆SQL Server中的数据库架构？

I would like to set up some solid testing on my project and the way I'd like to do it is have a commandline program that I can run that will copy just the structure of a database and not the actual data. Then I can run my tests on that new database.

我想在我的项目上设置一些可靠的测试,我想要的方法是有一个命令行程序,我可以运行它只复制数据库的结构,而不是实际的数据。然后我可以在新数据库上运行我的测试。

Ideas?

Update: someone said I should specify a language. I was thinking TSQL as that way if Sql Server runs, the script should run. Also, it's SQL Server 2005.

更新:有人说我应该指定一种语言。我正在考虑TSQL,如果Sql Server运行,脚本应该运行。此外,它是SQL Server 2005。

5 个解决方案

#1

You don't say which version of SQL Server you're using, if its 2005 look at DMO - Database Management Objects, a set of COM libraries that give you access to the functionality found in Enterprise Manager/Management Studio.

您没有说明您正在使用哪个版本的SQL Server,如果它的2005年是DMO - 数据库管理对象,这是一组COM库,可让您访问Enterprise Manager / Management Studio中的功能。

For 2008 we have SMO similar functionality but as .Net assemblies, MS have some good code examples. Their scripting example looks like waht you need :-

对于2008年,我们有SMO类似的功能,但作为.Net程序集,MS有一些很好的代码示例。他们的脚本示例看起来像你需要的: -

Lifted from their site we have

我们从他们的网站取消了

//Connect to the local, default instance of SQL Server. 
{ 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database. 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 

   //Define a Scripter object and set the required scripting options. 
   Scripter scrp = default(Scripter); 
   scrp = new Scripter(srv); 
   scrp.Options.ScriptDrops = false; 
   scrp.Options.WithDependencies = true; 

   //Iterate through the tables in database and script each one. Display the script. 
   //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
   Table tb = default(Table); 
   Urn[] smoObjects = new Urn[2]; 
   foreach ( tb in db.Tables) { 
      smoObjects = new Urn[1]; 
      smoObjects(0) = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         StringCollection sc = default(StringCollection); 
         sc = scrp.Script(smoObjects); 
         string st = null; 
         foreach ( st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 
} 

#2

you can use the following approch http://fahadaz.blogspot.com/2008/10/database-scripts-and-contiguous.html but it required your database create scripts.

您可以使用以下approch http://fahadaz.blogspot.com/2008/10/database-scripts-and-contiguous.html但它需要您的数据库创建脚本。

If you don't have those you can use the following article to generate them http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

如果您没有那些,您可以使用以下文章生成它们http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-和所有最对象 - 存储过程函数的触发器-表的视图约束和 - 全其他数据库的对象/

#3

The other answers work well if you want a thorough copy, but here is another approach that you may be intrigued by. If you are using LINQ to SQL, you can create a DBML file and then use the CreateDatabase() method on your data context. It does not copy the entire schema (omits some constraints, UDFs, Procs, etc...) but is useful in scenarios where you may want to operate on tables and perhaps partition your database testing.

如果你想要一个完整的副本,其他答案很有效,但这是另一种你可能会对此感兴趣的方法。如果您使用的是LINQ to SQL,则可以创建DBML文件,然后在数据上下文中使用CreateDatabase()方法。它不会复制整个模式(省略一些约束,UDF,Procs等等),但在您可能希望对表进行操作并可能对数据库测试进行分区的情况下非常有用。

Here are a few steps:

以下是几个步骤:

1. Create a Windows Forms App

   创建Windows窗体应用程序

2. Create a DBML file (LINQ to SQL classes) called Foo

   创建一个名为Foo的DBML文件(LINQ to SQL类)

3. Drag Tables/Objects you're interested in to the design surface

   将您感兴趣的表/对象拖到设计图面上

4. Close / Save

   关闭/保存

5. Some place in your application you could write code like:

   您的应用程序中的某些位置可以编写如下代码:

   FooDataContext fooData = new FooDataContext(@"connection string to new database");

   FooDataContext fooData = new FooDataContext(@“连接字符串到新数据库”);

   fooData.CreateDatabase();

Here is the documentation for the method I described above.

这是我上面描述的方法的文档。

#4

Sadly, the most automatic process we found was actually using one of those mouse control tools to actually use SSMS to copy the database. I'd MUCH rather do something else, but none of the answers actually fit our requirements.

遗憾的是,我们发现的最自动的过程实际上是使用其中一个鼠标控制工具来实际使用SSMS来复制数据库。我很乐意做别的事,但没有一个答案符合我们的要求。

#5

It is possible to do what you ask programmically. These are the steps:

你可以按程序做你想做的事情。这些是步骤:

1. Test if your "source_test" database exists, and if so, delete it.

测试您的“source_test”数据库是否存在,如果存在,请将其删除。

2. Connect to your source database,

连接到您的源数据库,

3. Create a backup of the source database

创建源数据库的备份

4. Restore the source database to "source_test" database while renaming the actual database files

在重命名实际数据库文件时,将源数据库还原到“source_test”数据库

5. Execute the SQL Server utility stored procedure, exec sp_msforeachtable 'truncate table ?'

执行SQL Server实用程序存储过程,exec sp_msforeachtable'truncate table?'

This creates a schematically identical database of your "source" database named "source_test". All the triggers, constraints, stored procedures, SQL Server accounts, and every other bit of data that isn't the data table contents or sequence numbers is preserved. All the sequence numbers will be reset to default value when their tables are truncated.

这将创建一个名为“source_test”的“源”数据库的示意图相同的数据库。将保留所有触发器,约束,存储过程,SQL Server帐户以及不是数据表内容或序列号的所有其他数据。当截断表时,所有序列号都将重置为默认值。

We use these 5 steps to clone our own SQL Server databases for testing and other uses. This works on SQL Server 2000 forward.

我们使用这5个步骤来克隆我们自己的SQL Server数据库以进行测试和其他用途。这适用于SQL Server 2000转发。

To backup the database, you execute the SQL statement in the form of:

要备份数据库,请以下列形式执行SQL语句:

"Backup database " + kstrDbName + " to disk = '" + strFullyPathedBackupFile + "' With Init";

"With Init" is a key option that tells SQL Server to overwrite the file specified if it exists. More information can be found by looking up the SQL Server documentation for "Backup database".

“使用Init”是一个关键选项,它告诉SQL Server覆盖指定的文件(如果存在)。通过查找“备份数据库”的SQL Server文档,可以找到更多信息。

On SQL Server 2005 and newer, you can use the SMO to restore the database, and specify the database's new name, as well as renaming the database files (the mdf and the log file). Example code of how to do this can be found in the Microsoft SQL Server SMO documentation on the Restore functionality.

在SQL Server 2005及更高版本上,您可以使用SMO还原数据库,并指定数据库的新名称,以及重命名数据库文件(mdf和日志文件)。有关如何执行此操作的示例代码,请参阅有关还原功能的Microsoft SQL Server SMO文档。

On SQL Server 2000, there is no SMO support, so it must be done manually, through executed SQL statements. That takes the form statement similar to:

在SQL Server 2000上,没有SMO支持,因此必须通过执行的SQL语句手动完成。这需要类似于以下形式的表单声明:

    string strSQL = "Restore database " + strDatabaseName + "from disk = '" + strSourcePathFile + "' with replace, " +
            " Move '" + strDatabaseName + "_Data' to '" + strDestinationPathFile + ".MDF', " +
            " Move '" + strDatabaseName + "_Log' to '" + strDestinationPathFile + ".LOG' " 

Then you use an Alter statement to rename the actual MDF and LOG files to match the new name. Yes, this means on SQL Server 2000, you cannot have the source database and the destination database. Sorry. It might be possible to do the rename of the MDF and LOG file at the same time as the restore, but if so, my team couldn't get it to work. We've moved on to new SQL Server databases, but I thought I'd pull the old code for completeness for this answer.

然后使用Alter语句重命名实际的MDF和LOG文件以匹配新名称。是的,这意味着在SQL Server 2000上,您不能拥有源数据库和目标数据库。抱歉。可以在还原的同时重命名MDF和LOG文件,但如果是这样,我的团队无法使其工作。我们已经转移到了新的SQL Server数据库,但我认为我会为了这个答案而完整地提取旧代码。

The alter statement is in the form of:

alter语句的形式为:

    string AlterDbSQL = " Alter database " + strDatabaseName + " MODIFY FILE(NAME = " + Path.GetFileNameWithoutExtension( strDestinationPathFile ) + "_DATA, NEWNAME = " + strDatabaseName + "_Data) " +
                        " Alter database " + strDatabaseName + " MODIFY FILE(NAME = " + Path.GetFileNameWithoutExtension( strDestinationPathFile ) + "_LOG, NEWNAME = " + strDatabaseName + "_LOG) "

Some notes: if all you are interested in is creating a test database, then you don't need to perform step 2 (connect to source) and step 3 (create backup) every time. Just create a "template" backup of your current database you want to test against by creating a backup, restoring it, and then truncating the tables. Back that database up. Now you have backup of a data content purged version of your database. It will be a smaller backup, so it will be faster to copy and faster to restore. All you need to do is add in your test data and it is ready for testing.

一些注意事项:如果您只对创建测试数据库感兴趣,那么每次都不需要执行步骤2(连接到源)和步骤3(创建备份)。只需通过创建备份,还原它,然后截断表来创建要测试的当前数据库的“模板”备份。回到那个数据库。现在,您可以备份数据库清除的数据库版本。它将是一个较小的备份,因此复制速度更快,恢复速度更快。您需要做的就是添加测试数据并准备好进行测试。