Creating copy of database - Fails due to use of 'Identity' specification

Question

I've been trying to figure out how to properly accommodate how to handle 'Identity' columns when generating a script to re-create the database.

The original reason why I need to generate a script for this is because I have to 'downgrade' a SQL database to an older version. I know everything in the database (v10.5) is compatible with the older version (v10.0). The issue I'm facing is that out of 3 different methods of copying the database, it always fails with the fact that it cannot maintain the original ID fields (which are identity).

Every table of mine has the very first column ID: Int = PK & Identity. I also have many cases where a table doesn't perfectly go sequential in this column, for example, 1, 2, 3, 5, 8, 12, 13, etc. That is simply because those records had been deleted in the past. But it seems as if it's impossible to re-insert the original ID numbers in the same order as they used to be...

So how do I copy (without backup/restore) a database in its entirety from Server A to Server B? NOTE: I can connect to both databases on both servers from the Management Studio. Also, the destination server is not mine, it is a shared hosted DB and I have access only to my database. I have no authority to change destination server settings.

I've tried the following:

• Generate script for entire database option
• Export database option
• Backup/Restore database - fails because of version mis-match

I'm guessing that I may just have to temporarily 'disable' the identity specification on all the tables, insert the data, then switch identity back on again. But I am horrible with writing scripts for manipulating the database structure. Data its self, I can do. But manipulating the database structure, I've gotten so used to using tools for this that I've never even taken the time to work with the scripts - and other than this particular scenario, hope that I never have to learn either.

Answer 1

I actually figured it out. I already knew that there must be a way to temporarily disable the identity specification, but the solution was a little different. Instead of 'disabling' and 're-enabling' the identity specification, there's another command (as in a comment above) called IDENTITY_INSERT which when switched on, it allows inserting values to an identity field - and you need to ensure it gets switched back off too. The IDENTITY_INSERT switch is per-connection-session, so it does not affect other sessions. As long as IDENTITY_INSERT is on, you can insert records with a specific value for that identity field - so long as it's still within the primary key constraints.

The actual solution was NOT to write a script with SET IDENTITY_INSERT MyTableName ON, but rather in the database export utility (in SQL management studio), when selecting the tables, select all the tables and choose the advanced setting to use IDENTITY_INSERT.