August 21, 2013 at 1:35 pm
I have a 2008 DB on my laptop, I need to temporarily set up a server version on 2005, here is the trouble I encountered:
1. I backed up and tried to restored, it failed (maybe it works if from 2k5 to 2k8)
2. I created new DB on 2k5, export all tables from 2k8 to 2k5, fine, working, however, all Keys/Constraints/Defaults are not there, I can't manually added those things.
3. I generated script for all objects, changed some settings and double checked to make sure the Keys/Constraints/Defaults are there, I ran the script on server, tables created with Keys/Constraints/Defaults
4. I then import data from 2k8, failed because of "Failure inserting into the read-only column "ID". (SQL Server Import and Export Wizard)"
So what should I do with minimum time and effort for this task?
Thank you very much.
August 21, 2013 at 9:44 pm
You cannot restore a database to an old version of SQL Server. So going from SQL 2008 to SQL 2005 is not an option. You can only restore to a later version (2005 to 2008 is OK, 2005 to 2008R2 is OK)
When you exported all tables, you probably didn't have the scripting options set so that the keys/constraints/defaults were included in the scripts. In management studio, go to Tools --> options. In the screen that pops up, check the options set in "SQL Server Object Explorer" for scripting.
It might be easier to create a snapshot publication - this should script out everything for you and deliver that with the data to your SQL 2005 database
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply