I have been trying to puzzle out how you can correctly accommodate how to deal with 'Identity' posts when producing a script to re-produce the database.

The initial reason I have to produce a script for the reason being I must 'downgrade' a SQL database for an older version. I understand my way through the database (v10.5) works with the older version (v10.). The problem I am facing is the fact that from 3 different techniques of copying the database, it always fails with the truth that it can't keep up with the original ID fields (that are identity).

Every table of mine has the initial column ID: Int = PK & Identity. I additionally have numerous cases when a table does not perfectly go consecutive within this column, for instance, 1, 2, 3, 5, 8, 12, 13, etc. That's due to the fact individuals records have been erased previously. However it appears as though you can't really re-place the initial ID amounts within the same order because they was once...

So how do you copy (without backup/restore) a database in the whole from Server A to Server B? NOTE: I'm able to connect with both databases on servers in the Management Studio. Also, the destination server isn't mine, it's a shared located DB and that i connect simply to my database. I've no authority to alter destination server configurations.

I have attempted the next:

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

I am speculating which i may have to temporarily 'disable' the identity specs on all of the tables, place the information, then switch identity back on again. But I'm horrible with writing scripts for adjusting the database structure. Data its self, I'm able to do. But adjusting the database structure, I have become accustomed to presenting tools with this that I have never even taken time to utilize the scripts - and aside from this particular scenario, hope which i never need to learn either.

I really figured it. I already understood that there has to be a method to temporarily disable the identity specs, however the solution was quite different. Rather than 'disabling' and Are-enabling' the identity specs, there's another command (as with a comment above) known as IDENTITY_INSERT that when started up, it enables placing values for an identity area - and you have to ensure it will get switched back away too. The IDENTITY_INSERT switch is per-connection-session, so it doesn't affect other periods. As lengthy as IDENTITY_INSERT is on, you are able to place records having a specific value for your identity area - as long as will still be inside the primary key constraints.

The particular solution wasn't to create a script with SET IDENTITY_INSERT MyTableName ON, but instead within the database export utility (in SQL management studio), when choosing the tables, choose all of the tables and select the advanced setting to make use of IDENTITY_INSERT.