SQL Server 2008 - 2005 Compatibility

Question

I want to use SQL Server 2008, but I'd also like to maintain a fall back position of switching back to SQL Server 2005. I know I cannot backup 2008 to restore 2005 and detach/attach won't work - what are my options?

[Update] To those asking why, two reasons -- one to develop from, where I can have access to 2008 features, but if a problem comes up or I don't find them useful enough, I can put my app back on 2005 and two, for run time so that if I find late in the game an unexpected problem.

Solution 1

Question in reply - why do you want 2005 as a fall-back position? Once you've tested your business-cycle on 2008 and are satisfied, shouldn't be any reason to move back.

You could try down-level transactional replication, but that's going to get very nasty very quickly for an entire database.

My advice - test, upgrade, don't look back.

Solution 2

SSIS or replication to move your data backwards. This isn't a great option. What's the reason for the 2005 fallback? After all, even if you can move the data, you won't be able to use 2008 things like the date datatype, or spacial data since that's not an option in 2005. What you're looking at is atypical.

Solution 3

I use red gate Sql Compare and Data compare to keep my databases synced between versions I used the SDK to create a job to keep the databases in sync automagically

I cannot restore the database from 2008 to 2005 but I can recreate it.

No i do not work for Red Gate

Comments

[Brent Ozar]

Yes, but if he wants to take the 2008 database back to a 2005 database, he can't do it, even if you set the compatibility level to the older version. Once it's attached to 2008, it's not going back.

[CPU_BUSY]

Perhaps like our case the data warehouse is still in 2005 - they stage the database before ETL to keep the load from the production database

[Paul Randal]

In that case as long as they don't use an 2008 features, down-level repl might work.

[GilaMonster]

Compatibility level just affect how SQL is parsed, what constructs are valid and how the query engine behaves. It does not affect the file structure, the internal database version or the system tables, and those are the things that prevent a SQL 2008 DB from being restored/attached to SQL 2005

[Hakan Winther]

I want to inform you that there is a bug in SQL Compare from Red-Gate. The parser in SQL Compare does not understand DATA_COMPRESSION on clustered indexes, and will give you an error. If you ignore the error, then you will lose the clustered index when you synchronize the database. This is a confirmed bug and a fix will be released as soon as it is corrected.