2

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.

Improve this question

5 Answers 5

Reset to default
5

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.

Improve this answer
2
  • 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
    – CPU_BUSY
    May 5, 2009 at 17:46
  • In that case as long as they don't use an 2008 features, down-level repl might work.
    – Paul Randal
    May 5, 2009 at 19:30
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.

Improve this answer
1

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

Improve this answer
1
  • 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.
    – Hakan Winther
    Jun 25, 2009 at 12:11
0

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?

Improve this answer
-2

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.

Improve this answer
2
  • 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.
    – Brent Ozar
    May 5, 2009 at 17:39
  • 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
    – GilaMonster
    May 7, 2009 at 11:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .