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.
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.
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
datedatatype, or spacial data since that's not an option in 2005. What you're looking at is atypical.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
Backup the 2005; then backup in the OS level ("backup the backup"), so you can have a copy of the original backups from 2005. Or am I understanding your question correctly. Why fallback to 2005 really?
You can run SQL Server 2005 and 2008 instances on the same machine. You can also run a database in 2005 compatibility mode (or even lower) on a SQL Server 2008 instance.