Considering Compatability Level

Question

Hello,
We have an older application that we have been running on Sql server standard 2000. The hardware for this application is failing, so we have to move it onto another platform. We have another Sql Server hosting another application, but that Sql Server is 2008.

So, I am thinking that I can
1. create the database in the 2008 server
2. set the compatability level to sql Server 2000
3. do a backup and restore of the Sql Database to import the databse into the new server.

I have a few questions:
1. Is this what the compatability level is used for?
2. Are there any known issues or gotchas for this?
3. I am thinking that this (the fact that 2008 is running the databse instead of 2000) should be totally transparant to the application. Obviously, we need to point the application to the new server. This process shouldn't change the structure of the actual database at all, correct?

Any advise, suggestions, etc would be much appreciated.

Thanks,
Jack

Answer 1

Hi Jack,

You can backup the SQL Server 2000 database and restore it on the instance of SQL Server 2008.  The database compatibility level will remain set at 80 (2000), nothing to change there. 

>> 1. Is this what the compatability level is used for?

Yes, but please be aware that it does not guarantee 100% compatibility. The Books Online topic related to compatibility levels (http://msdn.microsoft.com/en-us/library/bb510680.aspx)  says this... "Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting."

>> 2. Are there any known issues or gotchas for this?

Once you restore a database to a higher version (regardless of the compatibility level it is set to), you cannot backup the database on the higher version and restore it to a lower version. Restoring (or attaching) a database to a higher version will make some metadata changes that will not be backward compatible. That is, if your intention is to repair your hardware on the 2000 box and then return the database to the instance of SQL Server 2000, you will need to recreate the database from a script and bulk load the data. You will not be able to use backup/restore or detach/attach to go back.

3. I am thinking that this (the fact that 2008 is running the databse instead of 2000) should be totally transparant to the application. Obviously, we need to point the application to the new server. This process shouldn't change the structure of the actual database at all, correct?  

It might be transparent.  You'll obviously want to test first.  The process will change the structure of the database -- See the answer to question 2.

Regards,
Gail

---

Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights