Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Error copying database to live svr (with version)

Author  Topic 

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-10-02 : 11:32:56
I've created a database on SQL Server 2008 R2, but set the version as SQL Server 2005 (90).

When I try to attach this database on a server running SQL Server 2005 (9.00.3042.00), I get the following error:


The database 'MyDataBaseName' cannot be opened because it is version 661. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'MyDataBaseName'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)


Can anyone please explain why is this, when the version numbers should be the same? Is there any way to get around this issue please? I've tried different methods (Copy DataBase Wizard, Backup+Restore) but they are failing too.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-02 : 12:27:12
Once it's on 2008, you can't move it back to 2005. You've only set the compatibility level to 90, however it's still a 2008 database.

Yes you'll need to use the copy wizard or similar. I'd recommend generating the schema using SSMS wizard and then either bcp/SSIS or import/export wizard for the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-10-05 : 08:44:04
Hi

Thanks for that. I followed your advice and (after a lot of false starts) I achieved what I needed. One thing that kept happening was that the Export Data wizard threw an error relating to different table constraints, even though I specified Enable Identity Inserts. This kept happening regardless of whether I did all related tables at once, or one table at once. I also checked the data in the tables and nothing was wrong here. I had managed to successfully copy the data from the primary key tables specified within the constraint. After a load of attempts retrying this, it suddenly just started working for no apparent reason. Have you seen this type of behavior before?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-05 : 11:55:38
I never use that method to move databases as it is flaky and doesn't give you an exact copy. You really had no choice though. I have never needed to downgrade the database, so I've always been able to use the traditional methods like backup/restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-10-06 : 03:53:18
No problem - I always think lessons learned like this are never forgotten...!

Thanks for your help Tara :-)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 08:06:17
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -