Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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

share|improve this answer
1  
We have dozens of databases running this way. In fact we run a few databases in Compatibility 80 mode on SQL 2008 (Which is mode 100) –  Mark Henderson Dec 30 '09 at 21:00

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....

share|improve this answer

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.

share|improve this answer
    
Sorry Remus, I do not know where you are coming from. 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. –  samsmith Dec 31 '09 at 5:31
    
Sam, can you detail how to attach a database and keep it at lower version format? Afaik any attach/restore operation will result in Database '…' running the upgrade step from version … to version …. –  Remus Rusanu Dec 31 '09 at 18:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.