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 \[code\]ID: Int = PK & Identity\[/code\]. 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