在迁移到x64平台后，我们需要对SQL数据库进行版本调整，而旧版本在迁移过程中出现了问题。我们写了一个c#应用程序，使用sqlmo将所有SQL对象映射到一个文件夹:

                Root
                    ServerName
                       DatabaseName
                          Schema Objects
                             Database Triggers*
                                .ddltrigger.sql
                             Functions
                                ..function.sql
                             Security
                                Roles
                                   Application Roles
                                      .approle.sql
                                   Database Roles
                                      .role.sql
                                Schemas*
                                   .schema.sql
                                Users
                                   .user.sql
                             Storage
                                Full Text Catalogs*
                                   .fulltext.sql
                             Stored Procedures
                                ..proc.sql
                             Synonyms*
                                .synonym.sql
                             Tables
                                ..table.sql
                                Constraints
                                   ...chkconst.sql
                                   ...defconst.sql
                                Indexes
                                   ...index.sql
                                Keys
                                   ...fkey.sql
                                   ...pkey.sql
                                   ...ukey.sql
                                Triggers
                                   ...trigger.sql
                             Types
                                User-defined Data Types
                                   ..uddt.sql
                                XML Schema Collections*
                                   ..xmlschema.sql
                             Views
                                ..view.sql
                                Indexes
                                   ...index.sql
                                Triggers
                                   ...trigger.sql

然后，应用程序将新编写的版本与存储在SVN中的版本进行比较，如果存在差异，则更新SVN。 我们确定每晚运行一次流程就足够了，因为我们没有对SQL做那么多更改。它允许我们跟踪所有我们关心的对象的变化，并且允许我们在发生严重问题时重新构建完整的模式。

我写了这个应用程序一段时间前，http://sqlschemasourcectrl.codeplex.com/，它会扫描你的MSFT SQL db的经常你想，并自动转储你的对象(表，视图，procs，函数，SQL设置)到SVN。效果非常好。我把它和unduddle一起使用(它可以让我在签到时收到提醒)

每个推荐RedGate工具的人都要加1分，还有额外的建议和警告。

SqlCompare also has a decently documented API: so you can, for instance, write a console app which syncs your source controlled scripts folder with a CI integration testing database on checkin, so that when someone checks in a change to the schema from their scripts folder it's automatically deployed along with the matching application code change. This helps close the gap with developers who are forgetful about propagating changes in their local db up to a shared development DB (about half of us, I think :) ).

A caveat is that with a scripted solution or otherwise, the RedGate tools are sufficiently smooth that it's easy to forget about SQL realities underlying the abstraction. If you rename all the columns in a table, SqlCompare has no way to map the old columns to the new columns and will drop all the data in the table. It will generate warnings but I've seen people click past that. There's a general point here worth making, I think, that you can only automate DB versioning and upgrade so far - the abstractions are very leaky.

我建议使用比较工具为您的数据库临时设计一个版本控制系统。两个好的替代方案是xSQL模式比较和xSQL数据比较。

现在，如果您的目标是只对数据库的模式进行版本控制，那么您可以简单地使用xSQL schema Compare来生成模式的xSQL快照，并在版本控制中添加这些文件。然后，要恢复或更新到特定版本，只需将数据库的当前版本与目标版本的快照进行比较。

Also, if you want to have the data under version control as well, you can use xSQL Data Compare to generate change scripts for you database and add the .sql files in your version control. You could then execute these scripts to revert / update to any version you want. Keep in mind that for the 'revert' functionality you need to generate change scripts that, when executed, will make Version 3 the same as Version 2 and for the 'update' functionality, you need to generate change scripts that do the opposite.

最后，通过一些基本的批处理编程技能，您可以使用命令行版本的xSQL Schema Compare和xSQL Data Compare来自动化整个过程

免责声明:我隶属于xSQL。

我们使用DBGhost来管理SQL数据库。然后，您将脚本放在版本控制中构建一个新数据库，它将构建一个新数据库，或者将任何现有数据库升级到版本控制中的模式。这样，您就不必担心创建更改脚本(尽管您仍然可以这样做，例如，如果您想更改列的数据类型并需要转换数据)。

为了更快地将数据转储到源代码控制系统，您可以使用sysobjects中的版本信息来查看自上次以来哪些对象发生了更改。

设置:在每个数据库中增量地创建一个表，以保存上次检查时的版本信息(第一次运行时为空)。如果要重新扫描整个数据结构，请清除此表。

IF ISNULL(OBJECT_ID('last_run_sysversions'), 0) <> 0 DROP TABLE last_run_sysversions
CREATE TABLE last_run_sysversions (
    name varchar(128), 
    id int, base_schema_ver int,
    schema_ver int,
    type char(2)
)

正常运行模式:您可以从此sql中获取结果，并为您感兴趣的sql脚本生成sql脚本，并将它们放入您选择的源代码控制中。

IF ISNULL(OBJECT_ID('tempdb.dbo.#tmp'), 0) <> 0 DROP TABLE #tmp
CREATE TABLE #tmp (
    name varchar(128), 
    id int, base_schema_ver int,
    schema_ver int,
    type char(2)
)

SET NOCOUNT ON

-- Insert the values from the end of the last run into #tmp
INSERT #tmp (name, id, base_schema_ver, schema_ver, type) 
SELECT name, id, base_schema_ver, schema_ver, type FROM last_run_sysversions

DELETE last_run_sysversions
INSERT last_run_sysversions (name, id, base_schema_ver, schema_ver, type)
SELECT name, id, base_schema_ver, schema_ver, type FROM sysobjects

-- This next bit lists all differences to scripts.
SET NOCOUNT OFF

--Renamed.
SELECT 'renamed' AS ChangeType, t.name, o.name AS extra_info, 1 AS Priority
FROM sysobjects o INNER JOIN #tmp t ON o.id = t.id
WHERE o.name <> t.name /*COLLATE*/
AND o.type IN ('TR', 'P' ,'U' ,'V')
UNION 

--Changed (using alter)
SELECT 'changed' AS ChangeType, o.name /*COLLATE*/, 
       'altered' AS extra_info, 2 AS Priority
FROM sysobjects o INNER JOIN #tmp t ON o.id = t.id 
WHERE (
   o.base_schema_ver <> t.base_schema_ver
OR o.schema_ver      <> t.schema_ver
)
AND  o.type IN ('TR', 'P' ,'U' ,'V')
AND  o.name NOT IN ( SELECT oi.name 
         FROM sysobjects oi INNER JOIN #tmp ti ON oi.id = ti.id
         WHERE oi.name <> ti.name /*COLLATE*/
         AND oi.type IN ('TR', 'P' ,'U' ,'V')) 
UNION

--Changed (actually dropped and recreated [but not renamed])
SELECT 'changed' AS ChangeType, t.name, 'dropped' AS extra_info, 2 AS Priority
FROM #tmp t
WHERE    t.name IN ( SELECT ti.name /*COLLATE*/ FROM #tmp ti
         WHERE NOT EXISTS (SELECT * FROM sysobjects oi
                           WHERE oi.id = ti.id))
AND  t.name IN ( SELECT oi.name /*COLLATE*/ FROM sysobjects oi
         WHERE NOT EXISTS (SELECT * FROM #tmp ti
                           WHERE oi.id = ti.id)
         AND   oi.type  IN ('TR', 'P' ,'U' ,'V'))
UNION

--Deleted
SELECT 'deleted' AS ChangeType, t.name, '' AS extra_info, 0 AS Priority
FROM #tmp t
WHERE NOT EXISTS (SELECT * FROM sysobjects o
                  WHERE o.id = t.id)
AND t.name NOT IN (  SELECT oi.name /*COLLATE*/ FROM sysobjects oi
         WHERE NOT EXISTS (SELECT * FROM #tmp ti
                           WHERE oi.id = ti.id)
         AND   oi.type  IN ('TR', 'P' ,'U' ,'V'))
UNION

--Added
SELECT 'added' AS ChangeType, o.name /*COLLATE*/, '' AS extra_info, 4 AS Priority
FROM sysobjects o
WHERE NOT EXISTS (SELECT * FROM #tmp t
                  WHERE o.id = t.id)
AND      o.type  IN ('TR', 'P' ,'U' ,'V')
AND  o.name NOT IN ( SELECT ti.name /*COLLATE*/ FROM #tmp ti
         WHERE NOT EXISTS (SELECT * FROM sysobjects oi
                           WHERE oi.id = ti.id))
ORDER BY Priority ASC

注意:如果在任何数据库中使用非标准排序规则，则需要将/* COLLATE */替换为数据库排序规则。即COLLATE Latin1_General_CI_AI