Migration script for Renamed table is not found
sdave_assetic
Redgate › Posts: 3
Hi there,
I am testing rename for a table and added migration script as per example found on link: https://documentation.red-gate.com/disp ... +data+loss
however; while deploying using SQL Compare I am not seeing any migration script and checked deployment script which has drop and recreate statements rather than rename statement I have in migration script.
I am testing rename for a table and added migration script as per example found on link: https://documentation.red-gate.com/disp ... +data+loss
however; while deploying using SQL Compare I am not seeing any migration script and checked deployment script which has drop and recreate statements rather than rename statement I have in migration script.
Tagged:
Comments
You might need to set the 'Use migration scripts (V2)' option in the project setup, described here: documentation.red-gate.com - then the deployment script should respect the migration scripts you have set up.
Does that help?
I have test SQL compare with option you specified however; it still did not work.
These are the steps I did.
1. CREATE TABLE [dbo].[Test](
[Id] [UNIQUEIDENTIFIER] NOT NULL,
[Name] [NVARCHAR](255) NOT NULL,
CONSTRAINT [PK_Test-Id] PRIMARY KEY CLUSTERED
(
[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
ALTER TABLE [dbo].[Test] ADD CONSTRAINT [DF_Test-Id] DEFAULT (NEWSEQUENTIALID()) FOR [Id]
GO
2. CREATE TABLE [dbo].[MigrationHistory](
[Id] [UNIQUEIDENTIFIER] NOT NULL,
[DatabaseName] [NVARCHAR](50) NOT NULL,
[DACPACVersion] [NVARCHAR](50) NOT NULL,
[MigrationScriptFileName] [NVARCHAR](100) NOT NULL,
[Description] [NVARCHAR](1000) NOT NULL,
[RequestedBy] [NVARCHAR](50) NOT NULL,
[RequestedOn] [DATETIME] NOT NULL,
[DatabaseCreatedOn] [DATETIME] NULL,
[RedundantColumn] [NVARCHAR](50) NULL,
CONSTRAINT [PKC__DatabaseMigrationHistory-DACPACVersion-Id-MigrationScriptFileName] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[DACPACVersion] ASC,
[MigrationScriptFileName] 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
ALTER TABLE [dbo].[MigrationHistory] ADD CONSTRAINT [DF__DatabaseMigrationHistory-Id] DEFAULT (NEWSEQUENTIALID()) FOR [Id]
GO
ALTER TABLE [dbo].[MigrationHistory] ADD CONSTRAINT [DF__DatabaseMigrationHistory-MigrationScriptFileName] DEFAULT ('NO MIGRATION SCRIPT EXECUTED') FOR [MigrationScriptFileName]
GO
3. Added NULLABLE UNIQUEIDENTIFIER column ([TestId] [UNIQUEIDENTIFIER] NULL) to [MigrationHistory] table
4. Commit the changes to Development and Staging databases (Repeat Step 1 to 4 for Staging database)
5. On Development DB; Update TestId column to NOT NULL in MigrationHistory table and update values from Test table (Create the migration script for these changes and commit)
6. Open SQL Compare and compare Development and Staging database with Use Migration scripts (V2) option and try to deploy the changes. Note that even after mapping No migration scripts identified.
Any update on this old issue of losing data while renaming table?
we are using sql compare version : SQL Compare Command Line V11.3.3.25.
I understand that the using tool manually you can create migration script and select to run it.
but we would like to do it using SQL Compare command line.
1) if we create migration script how to configure the SQL Compare to look for this migration script file.
can someone help on this issue?
thanks
ravi
First of all, can I suggest you to upgrade your SQL Compare to the latest version?
By default, SQL Compare deploys the change with Migration scripts only from SQL Source Control or Script folder.
To do this using command line, for example if the source is from SQL Source Control, you need to:
1- create '/ScriptsFolderXML:<file path>' - The path to a text file containing XML that describes the location of a source control repository.
Please check this link for how to create it: https://documentation.red-gate.com/display/SC13/Switches+used+in+the+command+line#Switchesusedinthecommandline-/ScriptsFolderXML
2- run the command line like:
sqlcompare /sourcecontrol1 /revision1:4 /sfx:"D:\DB\test.xml" /db2:talkr_prod /s2:DEV-USER1\SQLSERVER2016 /sync
More details about the '/Sourcecontrol1' switch can be found here:
https://documentation.red-gate.com/display/SC13/Switches+used+in+the+command+line#Switchesusedinthecommandline-/Sourcecontrol1
If you want to ignore the migration script using command line, check this link for more details: https://documentation.red-gate.com/display/SC13/Options+used+in+the+command+line#Optionsusedinthecommandline-IgnoreMigrationScripts
Thanks.
Tianjiao Li | Redgate Software
Have you visited our Help Center?