Table rebuild in sql compare13 with TEXTIMAGE_ON

Varahalubabu

Table rebuild is happening when TEXTIMAGE_ON is there in the table creation statement



 if source and target have different file groups. It is happening in  SQL Compare13 but not in SQL Compare12 even if i use ifg option in sync step.

It is stopping us to setup automate deployments for DB. Can any one help us on this.

Script in Source:

CREATE TABLE [dbo].[MANAGE_FIELD_LABEL_NAME](

[FIELD_LABEL_ID] [BIGINT] IDENTITY(1,1) NOT NULL,

[FIELD_LABEL_NAME] [NVARCHAR](200) NULL,

[FIELD_DESCRIPTION] [VARCHAR](3000) NULL,

[FIELD_KEY] [NVARCHAR](MAX) NULL,

[FIELD_DESCRIPTION_TEST] [VARCHAR](100) NULL,

 CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] PRIMARY KEY CLUSTERED 

(

[FIELD_LABEL_ID] ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


Script in Target:

CREATE TABLE [dbo].[MANAGE_FIELD_LABEL_NAME](

[FIELD_LABEL_ID] [BIGINT] IDENTITY(1,1) NOT NULL,

[FIELD_LABEL_NAME] [NVARCHAR](200) NULL,

[FIELD_DESCRIPTION] [VARCHAR](3000) NULL,

[FIELD_KEY] [NVARCHAR](MAX) NULL,

 CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] PRIMARY KEY CLUSTERED 

(

[FIELD_LABEL_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [FG_Test]

) ON [FG_Test] TEXTIMAGE_ON [FG_Test]

I have used following command line syntax to replicate this.

C:\Program Files (x86)\Red Gate\SQL Compare 13>SQLCompare.exe /transactionIsolationLevel:SERIALIZABLE /exclude:Additional /scriptFile:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\CompleteMigrateSql.sql" /showWarnings /report:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\isa2y4hu.xml" /reportType:Xml /assertidentical /force /OutputWidth:1024 /scripts1:"D:\RedGateRandD\RedGateScript" /server2:AHS-LP-196 /database2:SyncDB /out:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\qua10g3s.log" /options:adus,we,cs,cfgps,dp2k,drd,nc,dacia,ib,icc,ict,ich,ic,icm,icn,idsn,idc,ie,ifg,if,ift,iip,isi,ii,iilp,iit,ik,ims,infr,ipi,ip,iq,isoa,isb,ist,isn,iscn,itst,iup,iw,iweo,iwe,iwn,nacm,oec,ucl

Varahalubabu

Tianjiao_Li ,
I have tried with SQL Compare 13.6.1.7928 still rebuilt is happening.
As you commented I am trying to update my SQL Compare to 13.6.3. as could not find such version in sql toolbelt. where can i find that version can you suggest us on this. Please find my above comment to find script which we are using for sync. Below is the resulted script.

Script Generated by SQL Comapare 13:

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

@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Dropping constraints from [dbo].[MANAGE_FIELD_LABEL_NAME]'

GO

ALTER TABLE [dbo].[MANAGE_FIELD_LABEL_NAME] DROP CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID]

GO

@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Rebuilding [dbo].[MANAGE_FIELD_LABEL_NAME]'

GO

CREATE TABLE [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]

(

[FIELD_LABEL_ID] [BIGINT] NOT NULL IDENTITY(1, 1),

[FIELD_LABEL_NAME] [NVARCHAR] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FIELD_DESCRIPTION] [VARCHAR] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FIELD_KEY] [NVARCHAR] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FIELD_DESCRIPTION_TEST] [VARCHAR] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

GO

@ERROR <> 0 SET NOEXEC ON

GO

SET IDENTITY_INSERT [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME] ON

GO

@ERROR <> 0 SET NOEXEC ON

GO

INSERT INTO [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]([FIELD_LABEL_ID], [FIELD_LABEL_NAME], [FIELD_DESCRIPTION], [FIELD_KEY]) SELECT [FIELD_LABEL_ID], [FIELD_LABEL_NAME], [FIELD_DESCRIPTION], [FIELD_KEY] FROM [dbo].[MANAGE_FIELD_LABEL_NAME]

GO

@ERROR <> 0 SET NOEXEC ON

GO

SET IDENTITY_INSERT [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME] OFF

GO

@ERROR <> 0 SET NOEXEC ON

GO

DECLARE @idVal BIGINT

SELECT @idVal = IDENT_CURRENT(N'[dbo].[MANAGE_FIELD_LABEL_NAME]')

IF @idVal IS NOT NULL

    DBCC CHECKIDENT(N'[dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]', RESEED, @idVal)

GO

@ERROR <> 0 SET NOEXEC ON

GO

DROP TABLE [dbo].[MANAGE_FIELD_LABEL_NAME]

GO

@ERROR <> 0 SET NOEXEC ON

GO

EXEC sp_rename N'[dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]', N'MANAGE_FIELD_LABEL_NAME', N'OBJECT'

GO

@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] on [dbo].[MANAGE_FIELD_LABEL_NAME]'

GO

ALTER TABLE [dbo].[MANAGE_FIELD_LABEL_NAME] ADD CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] PRIMARY KEY CLUSTERED  ([FIELD_LABEL_ID]) WITH (FILLFACTOR=80)

GO

@ERROR <> 0 SET NOEXEC ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON

GO

SET DATEFORMAT YMD

GO

SET XACT_ABORT ON

GO

COMMIT TRANSACTION

GO

@ERROR <> 0 SET NOEXEC ON

GO

-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.

IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1

BEGIN

    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)

    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')

    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'

    EXECUTE sys.xp_logevent 55000, @eventMessage

END

GO

DECLARE @Success AS BIT

SET @Success = 1

SET NOEXEC OFF

IF (@Success = 1) PRINT 'The database update succeeded'

ELSE BEGIN

@TRANCOUNT > 0 ROLLBACK TRANSACTION

PRINT 'The database update failed'

END

GO