Loading sql server 2005 backup file to sql server 2000

Question

Loading sql server 2005 backup file to sql server 2000

james s-327425

Newbie

Points: 9
More actions
June 4, 2007 at 6:32 am

#196060

Hi,
Does anyone know if sql server 2005's backup file is compatible with sql server 2000?
We want to take sql server 2005 created by a vendor software company and load it into our sql server 2000 instances.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Unfortunately, no. From BOL: Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

Just for curiosity.

Is it possible to set the compatibility to 80 in SQL Server 2005, then backup, then restore the backup to SQL 2000?

I have not tested, yet.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I would say no. I was asking Microsoft that question myself as we have been having performance issues on one of our SQL Server 2005 systems and that question was asked of me. You can not detach a database from SQL Server 2005 and atach it to SQL Server 2000, and though Friday wasn't that long ago, I think the tech also tested restoring a backup from 2005 to 200 and that didn't work either.

Best thing, test on test server and see what happens.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

No once you're on SQl 2005 you cannot go back to sql2000 unless you use DTS / SSIS to script and/or extract all data.

So even detatch / attatch wouldn't work.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 5

The reason you cannot restore/attach database of SQL Server 2005 to SQL 2000 is beacuse of Database version.

Database version in SQL 2000 is 539 & SQL 2005 is 610 so when you restore or attach database. SQL Server internally check the database version based on SQL Server 2000 or 2005.

When you upgrade SQL Server 2000 database to SQL Server 2005 the default compatibility level is 80. so after doing thorough testng then only i will advise to change compatibility level to 90. infact before upgrade, do the testing on test server. I hope this help.