Home / server / Questions / 98352
In Process
BenAlabaster
Asked: 2009-12-31 12:44:39 +0800 CST

Can a SQL Server 2000 database be hosted on SQL Server 2005/2008

  • 772

I have a SQL Server 2000 database backup, but I don't have SQL Server 2000 installed. I have SQL Server 2005 installed. I'd like not to have multiple pieces of DB server software installed. Is it possible in either SQL Server 2005 or SQL Server 2008 to host databases from the lesser systems? i.e. host a 2000 database in 2005?

sql-server

3 Answers

  1. There is a Compatibility mode in SQL 2005 (probably 2008) that will let you run your 2k database - we do this in production so it is stable. I don't believe the developers had any issue when we migrated to 2k5 while using compatibility mode. MS Linky

    • 6

  2. Its not possible. Each engine supports only its own database format, and the format changes from version to version. That means that a SQL Server 2005 SP1 will not be able to 'host' a database of version SQL Server 2005 RTM, nor one SQL Server 2005 SP2.

    However it is possible to upgrade a backup or an existing format. The engine will know how to upgrade from versions up to SQL 7 or SQL 2000, to it's current format. So you can restore a SQL 2000 backup on SQL 2005, but this operation will upgrade the former 2000 database to the 2005 format. Once upgraded, a database can never be downgraded. So if you attach a database to a SQL 2008, then is upgraded to a format SQL 2005 can no longer understand and it is not possible to down grade it back.

    I hope this makes it clear that there is a difference between hosting a database of an earlier version and running a current version in 'compatibility mode'. When you open a SQL 2000 database on SQL 2005 and set compatibility mode to 80, the upgrade has already occured and that database will never be possible to be used again in SQL 2000.

    The 'compatibility mode' is only needed if certain features are used by applications that require earlier operations mode, and the compatibility mode refers strictly to features that existed in earlier versions. For example a database is compatibility mode 80 will still have SQL 2005 specific views like 'sys.tables'.

    Update

    Despite the downvote flurry, my statement is still correct.

    • 0

  3. YES.

    This works great. I have done it many times.

    You can restore a SQL 2000 file to SQL 2005 and 2008, and can also attach a SQL 2000 db file directly. Upgrading the DB version is optional, and is only needed if you need to use newer TSQL, etc. against the db.

    You can even run dblib clients against SQL 2008 if the DB in question is SQL 2000 level....

    • 0