如何生成我的数据库脚本？

My primary reason for this is to keep track of database schema changes for my application. In SQL Server Management Studio I am able to generate a create script which creates the database but it doesn't contain any test data. Ideally when the script is run it should DROP the existing database (assuming it already exists) and then recreating it using this new script containing schema changes and test data from my development machine.

我的主要原因是跟踪我的应用程序的数据库架构更改。在SQL Server Management Studio中，我能够生成一个创建数据库的创建脚本，但它不包含任何测试数据。理想情况下，当脚本运行时，它应该删除现有数据库（假设它已经存在），然后使用包含架构更改和测试数据的新脚本重新创建它。

So how can I generate a script that will create a database with all the tables, stored procs, triggers, views, test data, etc?

那么如何生成一个脚本来创建一个包含所有表，存储过程，触发器，视图，测试数据等的数据库？

I've tried using the import/export functionality but that's no good because it doesn't seem to copy over stored procedures. Plus it would be nice to have a script so I can track changes to the schema using mercurial.

我尝试过使用导入/导出功能，但这并不好，因为它似乎没有复制存储过程。另外，拥有一个脚本会很好，所以我可以使用mercurial跟踪对模式的更改。

I am using SQL Server Express 2008 R2 along with SQL Server Management Studio.

我正在使用SQL Server Express 2008 R2和SQL Server Management Studio。

6 个解决方案

#1

You didn't mention which version of SQL Server, but in SQL 2008 this is very easy

您没有提到哪个版本的SQL Server，但在SQL 2008中这很容易

SQL 2008
Expand Databases
Right Click Database
Choose Tasks > Generate Scripts
Generate and Publish Dialog will open
Choose your objects (i.e. Tables, procs, etc)
Click Next On the Set Scripting
Options choose Advanced Options
Under General choose SCRIPT DROP AND CREATE - SCRIPT DROP AND CREATE
Types of Data To Script - Schema and Data
Close Advanced Window
Choose to save to file.

SQL 2008展开数据库右键单击数据库选择任务>生成脚本生成和发布对话框将打开选择您的对象（即表，过程等）单击下一步在设置脚本选项中选择高级选项在常规下选择脚本删除和创建 - 脚本删除和创建数据类型到脚本 - 架构和数据关闭高级窗口选择保存到文件。

#2

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

我写了一个名为SchemaZen的开源命令行实用程序来执行此操作。它比管理工作室的脚本快得多，它的输出更适合版本控制。它支持脚本化架构和数据。

To generate scripts run:

要生成脚本运行：

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

然后从脚本运行重新创建数据库：

schemazen.exe create --server localhost --database db --scriptDir c:\somedir

#3

Watch out for difference in database collation. If you develop on a database with a case insensitive collation and try and run the SSMS generated scripts against as database with a case sensitive collation then errors in case will break the scripts.

注意数据库整理的差异。如果您使用不区分大小写的排序规则在数据库上进行开发，并尝试使用区分大小写的排序规则将SSMS生成的脚本作为数据库运行，则大小写错误将破坏脚本。

#4

Try Microsoft SQL Server Database Publishing Wizard. This is a powerful flexible tool for scripting schema / data rom SQL Server.

尝试Microsoft SQL Server数据库发布向导。这是一个功能强大的灵活工具，用于编写SQL Server的架构/数据。

#5

Personally I use Microsoft Visual Studio 2010 Database Project with a Source Control Repository (SVN) to track changes to the schema.

我个人使用带有源代码控制存储库（SVN）的Microsoft Visual Studio 2010数据库项目来跟踪对架构的更改。

#6

Usually i make backups fom a database before start a new development on it.

通常我会在开始新的开发之前从数据库进行备份。

the best way is restore the backup when needed, i don't know how to get it by the script way!

最好的方法是在需要时恢复备份，我不知道如何通过脚本方式获取它！