What is the best way to use SQL Server 2008 as a development database, but ensure that the database is compatible with SQL Server 2005?
-
Note that you will not be able to restore your 2008 DB on 2005 instance. More info: stackoverflow.com/questions/17450114/…Aaron Hoffman– Aaron Hoffman2013-10-01 20:51:49 +00:00Commented Oct 1, 2013 at 20:51
Add a comment
|
3 Answers
This can be done via SQL Enterprise Manager or like this:
ALTER DATABASE <database>
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
Use 90 for 2005 compatibility.
This replaces the functionality used for previous releases, the stored procedure sp_dbcmptlevel.
Comments
You can set the database compatibility level, but there is no guarantee that this will cover all your bases. For example the new data types will be available on the SQL 2008 system, but they won't be available on the SQL 2005 server.
The only way to guarantee that everything will work from Dev to Prod is to put SQL 2005 in development.
Comments
Don't forget that there are also behavioral differences between the two versions, and something on 2008 may perform differently (and insufficiently) than 2005 all other things equal - this will obviously depend on a lot of factors about your data and application.
You're better off developing against the lowest common denominator and testing against the newer versions.
