Options
SQL Daca compare comparison keys [Not set]
pnovack84
Posts: 1 New member
Hi,
I'm comparing 2 DBs created with same schema and as a result I get almost 40% of tables without "Comparison key" set.
1) What could be the possible reason? Example table definition with issue is like:
CREATE TABLE [dbo].[R_T](
[R_T_ID] [INT] IDENTITY(1,1) NOT NULL,
[NAME] [VARCHAR](200) NOT NULL,
[USER_NAME] [NVARCHAR](255) NOT NULL,
[CREATED_TIME] [DATETIME] NOT NULL,
[PERMISSIONS] [VARCHAR](2000) NOT NULL,
[TEMPLATETYPE] [VARCHAR](50) NOT NULL,
[DEPLOYMENT_ID] [VARCHAR](256) NULL,
[CURRENT_FLAG] [BIT] NOT NULL,
CONSTRAINT [PK_R_T] PRIMARY KEY CLUSTERED
(......
2) I used to set the Comp key manually to overcome the strange issue, but now I need to compare DB to Backup and it looks like application forgets the custom value I set manually so its not usable anymore:
"You cannot specify custom comparison keys if you are using a backup as a data source; however, you can select an alternative unique index or unique constraint."
How can I set/use this "alternative unique index or unique constraint." is scenario DB vs Backup?
I'm comparing 2 DBs created with same schema and as a result I get almost 40% of tables without "Comparison key" set.
1) What could be the possible reason? Example table definition with issue is like:
CREATE TABLE [dbo].[R_T](
[R_T_ID] [INT] IDENTITY(1,1) NOT NULL,
[NAME] [VARCHAR](200) NOT NULL,
[USER_NAME] [NVARCHAR](255) NOT NULL,
[CREATED_TIME] [DATETIME] NOT NULL,
[PERMISSIONS] [VARCHAR](2000) NOT NULL,
[TEMPLATETYPE] [VARCHAR](50) NOT NULL,
[DEPLOYMENT_ID] [VARCHAR](256) NULL,
[CURRENT_FLAG] [BIT] NOT NULL,
CONSTRAINT [PK_R_T] PRIMARY KEY CLUSTERED
(......
2) I used to set the Comp key manually to overcome the strange issue, but now I need to compare DB to Backup and it looks like application forgets the custom value I set manually so its not usable anymore:
"You cannot specify custom comparison keys if you are using a backup as a data source; however, you can select an alternative unique index or unique constraint."
How can I set/use this "alternative unique index or unique constraint." is scenario DB vs Backup?
Tagged:
Answers
It often doesn't use the PK for the comparison by default (it doesn't say why) or I have to manually add the individual columns for each table. Even then, for some columns it will sometimes say 'the column data types prevent this mapping being used as part of custom comparison key', despite the fact that
a) the same column for the comparison database is accepted without issue
b) SQL Compare registers the tables as identical.
The screenshot below shows this issue.
When the applications maps things correctly it's invaluable, but I often dread having to use it because of this. I'm hoping this is just me missing some config option or doing something wrong.
Thanks for your post.
If you have a support contract and would like a support engineer to investigate your issue then please send us a ticket
Kind regards
Richard Lynch.
Redgate Software
https://forum.red-gate.com/discussion/comment/152306#Comment_152306