It''s Urgent and Important

Question

It''s Urgent and Important

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
August 8, 2007 at 11:53 am

#180037

Can we restore Sql Server 2005 backup in Sql Server 2000
Thank You.
Regards,
Raghavender Chavva

Viewing 11 posts - 1 through 10 (of 10 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

I believe the short answer is 'NO'.

If you need to get at the data, you could download the SQL Server 2005 Express version (free), restore the DB there, and then copy what you need back to your SQL Server 2000 instance.

And then again, I might be wrong ...
David Webb

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 2

Short and correct answer is definitely no, you cannot restore a native SQL 2005 backup on a SQL 2000 database for a lot of reasons (starting with new datatypes, collations, etc.)

Joe

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

Backup ..NO not possible.

Why not try to bcp/import the data to SQL 2000 fro SQL 2005.

Please review this link for more info:

http://www.aspfree.com/c/a/MS-SQL-Server/SQL-2000-Data-Transfer-is-a-Snap-with-SQL-2005/

Minaz

"More Green More Oxygen !! Plant a tree today"

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 4

Actually, I believe you can - if, and only if - you change the compatibility level of the database on SQL Server 2005 before making the backup. I have not tried it myself though, so I don't really know this.

In SQL Server Management Studio, you can change the compatibility level of a database by right clicking on the database and selecting Properties, then select the Options page and Compatibility level is right there below Collation and Recovery model.

For a native SQL Server 2005 database, the compatibility level should be SQL Server 2005 (90). Now, if you change this to SQL Server 2000 (80), that means you will basically have an SQL Server 2000 database running on a 2005 server. And, since it technically is a 2000 database, you should be able to make backups of this database that can be restored on a 2000 server. In theory, anyway. But like I said, I haven't actually tried this.

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 5

Okay, I just tried it and I stand corrected: No, you can NOT restore anything backed up from a 2005 server on a 2000 server.

As for compatibility level, this can make the database look like an SQL Server 2000 database to applications using it, but SQL Server still does several things different "under the hood", so it's not really a 2000 database anymore once you've loaded it on a 2005 server. Hence, once the database is on a 2005 server, you can not back it up from there and go back to a 2000 server, even if it has the 2000 compatibility level.

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)

Jason Hall Hall of Fame Points: 3906 More actions · Answer 6

Your best bet is to restore the database to a SQL Server 2005 server and then use the copy database utility in Management Studio to copy the database to a SQL Server 2000 instance.

Mal D SSC Enthusiast Points: 196 More actions · Answer 7

Hi Jason, the copy database will only work from 2005 to 2005 . Been there done that (and failed....) The answer to the Forum topic is No. as the Table structures are now totally different between 2005 and 2000.

CodeOn

Bob Fazio SSChampion Points: 10794 More actions · Answer 8

VERY STRONG SUGGESTION!

Set compatibility mode to 80 in 2005 and find a way to make it work. Your options for going back are few and all are painful.

Luis Neves SSC Journeyman Points: 85 More actions · Answer 9

Well, i think it's really not possible to restore the backup.

My sugestion for you is to use Database Publishing Wizard( can find all information here: http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard ) which can create a script of your database objects and data. You will just have to run it on against clean database.

If your code in sql server 2005 database is compatible with sql server 2000, then i think that will solve it for you. If not, try changing the compatibility mode to SQL Server 2000 (assuming it's not now), for finding possible problems.

Good Luck!

Luis Neves SSC Journeyman Points: 85 More actions · Answer 10

oops, don't forget to change on Publishing Options (before the finish button appear :-))

the "Script for target Database" to Sql Server 2000, and "Types of data to publish" to Schema and Data (although i think is the default).

And of course, you'll have to restore that backup to a SQL Server 2005 database first, and then yes, use Database Publishing Wizard for script creation.