使用SP克隆整个数据库

I'm trying to find out if this is possible, but so far I haven't found out any good solutions. What I would like to achieve is write a stored procedure that can clone a database but without the stored data. That means all tables, views, constraints, keys and indexes should be included but without any data. Can it be done?

我试图找出这是否可能，但到目前为止我还没有找到任何好的解决方案。我想要实现的是编写一个可以克隆数据库但没有存储数据的存储过程。这意味着应包含所有表，视图，约束，键和索引，但不包含任何数据。可以吗？

3 个解决方案

#1

I've successfully used the Microsoft SQL Server Database Publishing Wizard for this purpose. It's pretty straightforward, no coding needed. Here's a sample call:

我已成功使用Microsoft SQL Server数据库发布向导来实现此目的。它非常简单，无需编码。这是一个示例电话：

sqlpubwiz script -d DatabaseName -S ServerName -schemaonly C:\Projects2\Junk\ DatabaseName.sql

sqlpubwiz脚本-d DatabaseName -S ServerName -schemaonly C：\ Projects2 \ Junk \ DatabaseName.sql

I believe the default is to create both data and schema, but you can use the schemaonly parameter.

我相信默认是创建数据和架构，但您可以使用schemaonly参数。

Download it here

在这里下载

#2

Sure - your stored proc would have to read the system catalog views to find out what objects are in the database, determine their potential dependencies, and then create a single or a collection of SQL scripts which re-create the database, and execute those.

当然 - 您的存储过程必须读取系统目录视图以找出数据库中的对象，确定它们的潜在依赖关系，然后创建一个或一组SQL脚本来重新创建数据库并执行它们。

It's possible - not very nice and easy to do. Especially the dependencies between objects might cause more headaches than first meets the eye....

这是可能的 - 不是很好，很容易做到。特别是对象之间的依赖关系可能会导致比第一次遇到眼睛更多的麻烦....

You could also:

你也可以：

use something like SQL Server Management Studio (if you're on SQL Server - you didn't specify) and create the scripts manually, and just re-execute them on a separate server

使用类似SQL Server Management Studio的东西（如果你在SQL Server上 - 你没有指定）并手动创建脚本，只需在单独的服务器上重新执行它们
use a "diff" tool like Redgate SQL Compare to compare two servers and have the second one brought up to date

使用像Redgate SQL Compare这样的“diff”工具来比较两台服务器并让第二台服务器更新

#3

In SQL Server you can roll through the system tables (sys.tables, sys.columns, etc.) and construct things one at a time. It's going to be very manual and error prone at the beginning, but it should become systematic pretty quickly.

在SQL Server中，您可以浏览系统表（sys.tables，sys.columns等）并一次构建一个。它在开始时会非常手动且容易出错，但它应该很快就会变得系统化。

Another way to do it is to write something in .Net using SMO. Check out this link:

另一种方法是使用SMO在.Net中编写一些东西。看看这个链接：

http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated

#1