Post reply

Restore 2005 database on a 2000?

  • March 18, 2008 at 10:21 am

    #185297

    Let me qualify this question by saying that the database is already running with the compatibility level set to 8. We are not using ANY 2005-specific features. Please do not suggest simply upgrading to 2005.

    Is it possible to create a backup of a database running on SQL 2005 and restore it on a SQL 2000 instance? Does setting the compatibility level in the database options have any effect? If this cannot be done, what is the suggested route for moving the structure AND data back to a previous version?

    Why would we want to do this? Some of our clients still use SQL Server 2000, but we would like to use SQL 2005 for our in house development. I know running in compatibility mode would make this possible for scripts, but how can we move data if we needed to?

    Thanks in advance,

    -Mark

  • March 18, 2008 at 11:06 am

    #790996

    First, the answer to the question: NOPE

    Second, a possible solution: Get Red Gates' SQL Compare and SQL Data Compare. Create an empty SQL 2000 database and use SQL Compare to create the scripts to make your empty database schema match the source. Then use SQL Data Compare to synch the data over.

    😀

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • March 18, 2008 at 1:04 pm

    #791075

    I agree with Jason. I did the same thing. after SQL Data compare, created Logins and users and mapped them.

    You can download a 30 day trial version.

    SQL DBA.

  • March 18, 2008 at 1:08 pm

    #791079

    Thanks for the concise answer and suggestion. We have an in-house tool (similar to the redgate product) that matches up schemas. I was more interested in the data. For those that might be interested, I also came across this:

    http://www.codeplex.com/sqlhost/

  • March 30, 2008 at 8:39 am

    #795699

    I agree you cannot restore a Sql 2005 backup to Sql 2000 server but you have an alternative in Sql 2005. You can make use of Database Publishing Wizard to move the database from Sql 2005 to Sql 2000. Refer this link which describes the same. Changing the compatibility to 80 will not have any effect. http://sql-articles.com/index.php?page=articles/migrating_2005_2000.html

    [font="Verdana"]- Deepak[/font]

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

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