How to take DB backup as SQL2000 version

  • Hi,

    AS per my knowledge, if database backup from SQL-2005 required to restored in SQL 2000, then one way is to create a blank database and use Import wizard to acquire the data from SQL 2005 database. But I don't think this is the correct approach as the targeting system may be in the remote (or client) place.

    Is there any way to take the backup in SQL 2000 (or SQL version 8.0) or SQL 7.0 format itself while taking the backup from SQL 2005? Please guide me.

  • No, you have to export everything to get from 2005 to 2000. If the target is in a remote place, you could install a local version of 2000, import data from 2005 to 2000 locally, then take a backup of your local 2000 and send it to the remote location.

  • Oh. I remember, when I worked with SQL 2005 version for first time, (some 2 years before), there was an option in SQL 2005 to restore the database as version 7 or 8 compatibility. or the option may be to take backup in version 7 or 8 compatibility. I am not remembering. Now am unable to find that option from our installed server. Any more help?

  • You can change the compatibility level of the database in SQL Server 2005 all you want. What you can't do is take a backup of the database and restore it to an older version of SQL Server. Once the database is attached to SQL Server 2005, it is a SQL Server 2005 database regardless of compatibility level. That only controls what syntax and features are available in a specific database. For instance, a SQL Server 2005 database using the compatibility level 80 can't use CTE's.

  • The compatibility level determines how the code is interpreted by the server. It allows keywords that did not exist in 2000 (or 7) but do exist in 2005 to still be used in code. For example, CROSS, is a new keyword in 2005, but it was a valid field name in 2000.

    Once a database is on 2005, or 2008, it's structure is changed. So it can't be attached back on an earlier version. On top of that, the backup routines work as 2005, so a backup will be in the new format.

    As mentioned, only an export/import of the data will move it back to an earlier version.

  • rajeev_vandakar-782919 (1/8/2010)


    Oh. I remember, when I worked with SQL 2005 version for first time, (some 2 years before), there was an option in SQL 2005 to restore the database as version 7 or 8 compatibility. or the option may be to take backup in version 7 or 8 compatibility.

    There has never been an option to backup or restore a database as a version 7 or 8 database. What there is is the ability to specify the compatibility mode, which controls what T-SQL constructs are valid and how the query processor handles them. It does not in any way change the structure of the database or backup file however. A database on SQL 2005 is a SQL 2005 database and that cannot be changed.

    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
  • Thank you guys. As you all told, the option might be to restore the database as version 7/8 in SQL 2005. Now the only way for me is to migrate the data from SQL 2005 to 2000 before delivery to client. Thank you very much.

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

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