Can sql server 2005 mdf be attached to sql server 2000

Question

Post reply

Can sql server 2005 mdf be attached to sql server 2000

Douglas Murphy

SSC Rookie

Points: 45
More actions
February 8, 2010 at 5:51 pm

#215398

I have SQL Server 2005 on my computer. I need to upsize an Access database to this and be able to attach the resulting file to a SQL server 2000 instance on another machine. I am wondering if I upsize the access back end file to SQL Server 2005 with compatibility mode set to 80 and then detach it if I can then attach it to SQL Server 2000. Can anyone give me some guidance on this?
Thank you in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

I think it would not be possible..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Douglas Murphy (2/8/2010)
I am wondering if I upsize the access back end file to SQL Server 2005 with compatibility mode set to 80 and then detach it if I can then attach it to SQL Server 2000.

No you cannot.

Regardless of compatibility level, a database attached to a SQL 2005 instance is a SQL 2005 database and cannot be attached or restored onto a SQL 2000 database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Douglas Murphy SSC Rookie Points: 45 More actions · Answer 3

This is where I show how ignorant I am. In most other Microsoft applications you can save to an older version. Is this possible with a SQL Server database?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No, it is not.

The only way is to script all the objects, export all the data and recreate the DB on a lower version.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

Douglas Murphy (2/9/2010)
Is this possible with a SQL Server database?

I guess Gail and I answered it already, You cannot add the MDF files of SQL Server 2005 to SQL Server 2000.

Edited: Haven't looked at Gail's post when starting the reply..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Douglas Murphy SSC Rookie Points: 45 More actions · Answer 6

Hello Gail and Bru,

Thank you for answering my questions. Looks like I'll need to set up a sql server 2000 virtual machine to do this. Was hoping to do it in 2005 and then convert to 2000.

Doug

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

Douglas Murphy (2/9/2010)
This is where I show how ignorant I am. In most other Microsoft applications you can save to an older version. Is this possible with a SQL Server database?

There's a big difference between saving a Word or Excel file to an earlier format and "downgrading" a SQL Server database file. Desktop applications and server type products don't play inn the same ball park.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]