SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Error copying database to live svr (with version)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 10/02/2011 :  11:32:56  Show Profile  Reply with Quote
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

USA
36597 Posts

Posted - 10/02/2011 :  12:27:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
328 Posts

Posted - 10/05/2011 :  08:44:04  Show Profile  Reply with Quote
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

USA
36597 Posts

Posted - 10/05/2011 :  11:55:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
328 Posts

Posted - 10/06/2011 :  03:53:18  Show Profile  Reply with Quote
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

USA
36597 Posts

Posted - 10/06/2011 :  08:06:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000