Error copying database to live svr (with version)

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

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?

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

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 :-)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources