April 28, 2014 at 12:36 pm
We have a database running on a SQL 2008 R2 server.
The database has a table with a single column for generating an identity value as a unique ID used elsewhere.
There's a stored proc that runs this script
BEGIN TRAN
SAVE TRAN GENERATOR_TRAN /* Sets a savepoint within a transation */
INSERT INTO GENERATOR_TABLE WITH (ROWLOCK) ([DUMMY]) VALUES (NULL) OPTION (MAXDOP 1)
ROLLBACK TRAN GENERATOR_TRAN /* Rollback so that the ID is not stored in the table and the table remains empty, but we do have the new ID */
COMMIT TRAN
RETURN IDENT_CURRENT('GENERATOR_TABLE')
Because the INSERT is rolled back this table never contains a row.
We have used Copy Database to create what we think is a duplicate copy of the database. However, when we do that, DBCC CHECKIDENT('generator_table') on the source db returns 28657 but on the copied db returns NULL.
Can anyone shed some light on this behaviour? Are identity columns reseeded in a copy database operation?
Thanks
April 28, 2014 at 6:36 pm
If you did backup and restore instead of copy, you would not have this problem.
April 29, 2014 at 6:26 am
A copy of the database just exports the scripts to define the tables. It's not going to export any other settings such as the current identity. You can use DBCC CHECKIDENT to reseed the value to what it should be. A backup and restore is going to move everything over (but it includes the data).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2014 at 6:52 am
Hi, gents ...
Grant, the Copy Database task does copy both structure and data (although on rereading what you wrote I think you meant that). It's only the identities that aren't consistent (and possibly some other things I haven't recognized).
Empirical evidence of course confirms that backup/restore means the target is an exact duplicate of the source whereas Copy Database task is "pretty close".
I guess my real question is, is this actually documented anywhere? I can't imagine I'm the first person to stumble on this and yet searching gave me no information on it.
Thanks for your input!
April 29, 2014 at 7:56 am
The documentation of the copy database is unclear as to exactly what it does. It says it moves meta-data about the objects, but I don't see details.
Basically, it creates a script for the tables and it moves the data. It's probably making assumptions that when you insert data the identity fields will naturally arrive at the appropriate place. To a degree, what you're doing, while fully functional, is a bit of a cheat. So there's no way to know that you're doing something that's somewhat non-standard that would require it to do, what under relatively normal circumstances, would be an extra and unnecessary step. It also doesn't move your statistics either (unless you specify that) but relies on the load process to recreate them. Scripting out a database is very different than doing a backup. This is just a couple of the quirks that you might run into. There are a bunch of other restrictions and stuff listed out there in the documentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2014 at 8:02 am
"To a degree, what you're doing, while fully functional, is a bit of a cheat." - that's the other part of the issue.
It's vendor code, and I can't put my finger on why but it seems wrong to do what they're doing that way. They have three tables containing versions of the same data where they want the same ID value used in each - don't know why they didn't just retrieve the ID from the original insert in the first table to use for the other two.
I suspect the bottom line for the original question is "Friends don't let friends use Copy Database". Live and learn.
Thanks for the link!
April 29, 2014 at 8:09 am
Wasn't trying to be critical of it, but that is the root of the problem. You have empty tables that are driving data without a correlation between the two that's obvious to a dumb-old scripting engine. I'll bet SQL Compare would screw it up too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2014 at 8:31 am
Out of curiosity, I ran SQL Compare on the correct db and a copy with the wrong identity.
I don't see anywhere in SQL Compare where identities are compared?
April 29, 2014 at 8:54 am
If you go to options when setting up a compare, by default, it's supposed to check the identity seed, but you can turn it off.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply