SQL Compare是一款比較和同步SQL Server數據庫結構的工具。現有超過150,000的數據庫管理員、開發人員和測試人員在使用它。當測試本地數據庫，暫存或激活遠程服務器的數據庫時，SQL Compare將分配數據庫的過程自動化。

點擊下載SQL Compare試用版

第三版

這次，我們決定更改出版物，我們不僅僅只涉及一個主題，還允許應用多個子主題。我們這樣做是為了說明遷移步驟，該步驟將需要在遷移腳本中添加一些其他遷移代碼。

使用我們剛剛保存的v2.1.7構建腳本，我們type從titles表中刪除該列并創建兩個新表。其中一個是稱為的標簽列表，TagName另一個是稱為的標簽列表，TagTitle用于將一個或多個標簽與標題相關聯，但是每個標題只有一個主標簽。同樣，我使用構建腳本來執行此操作，因為更改此表并創建另外兩個表會產生影響。

CREATE TABLE [dbo].[titles](
    [title_id] [dbo].[tid] NOT NULL,
    [title] [nvarchar](120) NOT NULL,
    [pub_id] [char](10) NULL,
    [price] [money] NULL,
    [advance] [money] NULL,
    [royalty] [int] NULL,
    [ytd_sales] [int] NULL,
    [notes] [nvarchar](max) NULL,
    [pubdate] [datetime] NOT NULL,
 CONSTRAINT [UPKCL_titleidind] PRIMARY KEY CLUSTERED 
(
    [title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE TagName (TagName_ID INT IDENTITY(1, 1) PRIMARY KEY, Tag VARCHAR(20) NOT NULL UNIQUE);
go
CREATE TABLE TagTitle
  (
  TagTitle_ID INT IDENTITY(1, 1),
  title_id dbo.tid NOT NULL REFERENCES titles (title_id),
  Is_Primary BIT NOT NULL DEFAULT 0,
  TagName_ID INT NOT NULL REFERENCES TagName (TagName_ID),
  CONSTRAINT PK_TagNameTitle PRIMARY KEY CLUSTERED (title_id ASC, TagName_ID) ON [PRIMARY]
  );

當您為新版本（2.1.8）運行完整的構建腳本時，您會看到構建錯誤，因為有幾個依賴的報告存儲過程reptq2和reptq3，它們使用type需要更改的舊列。

消息207，級別16，狀態1，過程reptq2，第4行[批處理開始行459]
無效的列名“類型”。
消息207，級別16，狀態1，過程reptq3，第10行[批處理開始行475]
無效的列名“類型”。

不過，您將擁有重構的表，因此可以為它們設計新的代碼而不會出現太多問題（我不會在這里顯示它，但是您很快就會在遷移腳本中看到它）。您還必須修復DEFAULT舊type列的約束，因此它引用新Tag列。

當然，要測試這個新版本，我們現在需要用當前版本（2.1.7）中的數據填充它，但是這次我們需要制定數據遷移腳本以將數據移到舊type列中到新表中，并填充其他新列。

解決了數據遷移的所有問題并運行了所有測試后，我們將使用v2.1.8標記新的開發版本，并使用帶有該版本的SQL Compare作為源代碼和源代碼的內容目錄作為目標，以便更新對象腳本并保存“ 2.1.7 to 2.1.8”遷移腳本，這時會發出警告。

我們需要打開剛剛保存的遷移腳本并對其進行編輯。我們創建一個臨時表，它是title的一個版本。我們使用它來將數據添加到兩個新表中。方便地，更改后的存儲過程reptq2可以用作方便的單元測試（有關完成的腳本，請參見migration_2-1-7_to_2-1-8.sql）。如果這兩個過程給出的結果與以前的版本相同，那么我們很可能會早點回家。如果要繼續學習，則需要使用SQL Compare生成腳本，然后在腳本中添加“插入代碼”注釋標記的部分。

/*
Run this script on :
Script created by SQL Compare version 13.4.5.6953 from Red Gate Software Ltd at 12/05/2020 09:35:47
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
--inserted code
Declare @version varchar(25);
SELECT @version= Coalesce(Json_Value(
  ( SELECT Convert(NVARCHAR(3760), value) 
      FROM sys.extended_properties AS EP
      WHERE major_id = 0 AND minor_id = 0 
        AND name = 'Database_Info'),'$[0].Version'),'that was not recorded');
IF @version <> '2.1.7'
  BEGIN
  RAISERROR ('The Target was at version %s, not the correct version (2.1.7)',16,1,@version)
  SET NOEXEC ON
  END
go
PRINT N'Saving TITLES table to temporary table'
SELECT titles.title_id, titles.title, titles.type, titles.pub_id, titles.price,
  titles.advance, titles.royalty, titles.ytd_sales, titles.notes,
  titles.pubdate
  INTO #titles
  FROM [dbo].[titles];
IF @@ERROR <> 0 SET NOEXEC ON
GO
--end of inserted code
PRINT N'Dropping constraints from [dbo].[titles]'
GO
ALTER TABLE [dbo].[titles] DROP CONSTRAINT [DF__titles__type__07F6335A]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[titles]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[titles] DROP
COLUMN [type]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TagName]'
GO
CREATE TABLE [dbo].[TagName]
(
[TagName_ID] [int] NOT NULL IDENTITY(1, 1),
[Tag] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK__TagName__3109E9F88C8DE0AD] on [dbo].[TagName]'
GO
ALTER TABLE [dbo].[TagName] ADD PRIMARY KEY CLUSTERED  ([TagName_ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding constraints to [dbo].[TagName]'
GO
ALTER TABLE [dbo].[TagName] ADD UNIQUE NONCLUSTERED  ([Tag])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TagTitle]'
GO
CREATE TABLE [dbo].[TagTitle]
(
[TagTitle_ID] [int] NOT NULL IDENTITY(1, 1),
[title_id] [dbo].[tid] NOT NULL,
[Is_Primary] [bit] NOT NULL DEFAULT ((0)),
[TagName_ID] [int] NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_TagNameTitle] on [dbo].[TagTitle]'
GO
ALTER TABLE [dbo].[TagTitle] ADD CONSTRAINT [PK_TagNameTitle] PRIMARY KEY CLUSTERED  ([title_id], [TagName_ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[reptq2]'
GO
ALTER PROCEDURE [dbo].[reptq2] AS
select 
    case when grouping(TN.tag) = 1 then 'ALL' else TN.tag end as type, 
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
    avg(ytd_sales) as avg_ytd_sales
 FROM titles INNER JOIN tagtitle
ON TagTitle.title_id = titles.title_id
INNER JOIN dbo.TagName AS TN 
ON TN.TagName_ID = TagTitle.TagName_ID
where pub_id is NOT NULL AND is_primary=1
group by pub_id, TN.tag with rollup
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[reptq3]'
GO
ALTER PROCEDURE [dbo].[reptq3] @lolimit money, @hilimit money,
@type char(12)
AS
select 
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
    case when grouping(TN.tag) = 1 then 'ALL' else TN.tag end as type, 
    count(titles.title_id) as cnt
from titles INNER JOIN tagtitle
ON TagTitle.title_id = titles.title_id
INNER JOIN dbo.TagName AS TN 
ON TN.TagName_ID = TagTitle.TagName_ID
where price >@lolimit AND is_primary=1 AND price <@hilimit AND TN.tag = @type OR TN.tag LIKE '%cook%'
group by pub_id, TN.tag with rollup
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding foreign keys to [dbo].[TagTitle]'
GO
ALTER TABLE [dbo].[TagTitle] ADD FOREIGN KEY ([TagName_ID]) REFERENCES [dbo].[TagName] ([TagName_ID])
GO
ALTER TABLE [dbo].[TagTitle] ADD FOREIGN KEY ([title_id]) REFERENCES [dbo].[titles] ([title_id])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering extended properties'
GO
BEGIN TRY
    EXEC sp_updateextendedproperty N'Database_Info', N'[{"Name":"Pubs","Version":"2.1.8","Description":"The Pubs (publishing) Database supports a fictitious bookshop.","Modified":"2020-05-06T13:57:56.217","by":"PhilFactor"}]', NULL, NULL, NULL, NULL, NULL, NULL
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(max);
    DECLARE @severity int;
    DECLARE @state int;
    SELECT @msg = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE();
    RAISERROR(@msg, @severity, @state);
    SET NOEXEC ON
END CATCH
GO
--inserted code
INSERT INTO TagName (Tag) SELECT DISTINCT type FROM #titles;
IF @@ERROR <> 0 SET NOEXEC ON
INSERT INTO TagTitle (title_id,Is_Primary,TagName_ID)
  SELECT title_id, 1, TagName_ID FROM #titles 
    INNER JOIN TagName ON #titles.type = TagName.Tag;
IF @@ERROR <> 0 SET NOEXEC ON
DROP  TABLE #titles
go
--end of inserted code
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

我們已經經歷了三個版本。第一個是最小的更改，只是為了演示如何進行版本檢查。第二個將所有這些varchar數據類型更改為更正確的NVARCHAR數據類型。最后一個使書籍分類系統更加有用，并更新了存儲過程以使用新系統。最后一個需要數據遷移。現在，我們可以進行全部測試。我們想知道是否可以遷移從Internet下載的原始版本的數據庫，并將其升級到與源目錄中的原始版本相同的版本。

由于原始備份已從互聯網上消失了很長時間，因此我在Github存儲庫的備份目錄中添加了一個副本。現在，我們以正確的順序將四個腳本應用于該腳本...

1. AddInitialVersion.sql –僅將版本添加到原始Pubs
2. migration_2-1-5_to_2-1-6.sql –從2.1.5遷移到2.1.6：僅更新PubInfo表的數據類型。
3. migration_2-1-6_to_2-1-7.sql –從2.1.6遷移到2.1.7：將所有varchar類型的數據類型更新為NVarchar。
4. migration_2-1-7_to_2-1-8.sql –從2.1.7遷移到2.1.8：拆分“標題”表以允許分配多種圖書類型。演示數據遷移。

出現的一個問題是沒有顯式名稱聲明的約束的問題。這對于臨時表和表變量來說很好，但是對于基表來說不是一個好習慣，因為它會使任何比較工作變得更加困難。當您使用一系列現有的遷移腳本來創建具有完整現有數據的版本時，它也會產生連鎖反應。如果您是從使用懶惰的約束定義的構建腳本創建Pubs的初始副本的，那么這些遷移腳本在應用于數據庫時有時會失敗，因為這些約束是在元數據中內部指定的，例如'PK__TagName__3109E9F88C8DE0AD'。該隨機數將不會重復。這就是為什么我提供Pubs作為備份而不是發布的構建腳本的原因。

結論

在數據庫開發過程中，優秀的開發人員應自由使用最佳工具來完成當前任務。您可能需要使用導出新表設計的ER圖表工具。在項目的某個時刻，您可能想要使用傳統的構建腳本，表構建器工具，甚至是文本編輯器以及SSMS。如果您可以生成對象級腳本和遷移腳本的可交付成果，那么可以。

為了可靠地部署數據庫更改，我們需要在版本控制中同時使用每個版本的對象級源和在版本之間移動的遷移腳本。當某個版本成為發行候選版本時，將創建遷移腳本，該腳本將使用先前發行版中的數據庫。SQL Compare可以提供此腳本的“第一手資料”，只要SQL Compare無法滿足所有要求，便可以與自定義遷移代碼結合使用。當然，您可以按順序運行各個版本到版本的腳本，但是其中可能要管理的腳本太多，并且您可能會因無法在集成測試中幸存下來的設計思想而來來往往。每個版本一個遷移腳本更易于管理。

最后，自動化很重要。由于在單元測試，集成測試，自動化構建和“辦公室工作”（檢查日志，團隊協作，文檔，問題管理和報告）的速度和數量方面的期望越來越高，因此自動化成為實現更加愉快和富有成效的工作生活。在即將發表的文章中，我將展示開發階段的各種任務，如本文所述，將其插入自動化的SQL Change Automation過程中。

相關產品推薦：

SQL Prompt：SQL語法提示工具

SQL Toolbelt：Red Gate產品套包

SQL Monitor：SQL Server監控工具