Hi, We have a couple of SQL2000 databases that we are migrating to 2012. Everything is looking good in testing and we are preparing our go-live plans... We will be migrating over a weekend when it is quiet but there will be a few transactions.. but we will not really be sure if all performs OK until Monday morning... IF we hit a show-stopper once we have migrated, is it possible to go backwards? ie, could we take a backup of the migrated 2012 database, restore it onto a 2008 database... and then back to the original 2000 hardware ??? Or do we just need to be prepared to fire fight on the new hardware and keep going forward? Thanks in advance for your thoughts and comments?
Not that easy I'm afraid. You cannot restore a database onto a lower version. Take backups of the SQL2000 databases at the point of migration - at least then you have a point to go back to. You could then query to get any modifications made on SQL 2012 since the migration and apply that data back to 2000 - although it does kind of depend on the nature of the 'show-stopper' if for some reason it has mangled the data, then there's not a lot you can do.
With this being a migration and what appears to be a side by side migration (and not an upgrade) you can take some solace in that the original database should not be affected. Just restore those backups onto the newer versions (you can do the step-up to 2008 then 2012 as you mentioned). My recommendation would be that before you get anywhere close to doing this on the production box, you need to do a few trial runs. Perform the process for these database migrations in a QA or dev environment where the repercussions are significantly lower. In addition, performing this step should allow you to force a regression test of the applications touching these databases. Without a test environment to perform at least one dry run and allow for a thorough testing of the applications, proceeding is rather risky and prone to mistakes.