question

DBAccident avatar image
DBAccident asked

Restoring a 2012 database back to SQL 2000

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?
sql 2012migratationsql 2000
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Many thanks for all of your comments and suggestions. I am now planning to test the prod migration (side by side) as many times as necessary and also investigate how to 'load' the new box and see how it performs under pressure. I'm also going to investigate performance monitoring and capture a couple of weeks worth of data from before the migration so we can look at 'before and after' stats if needed. Thanks again and best regards.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Kev - while that was the answer I feared, your suggestion is a good one! I'm pretty happy with the SQL data migration etc, I am more concerned about changing the database and hardware all in one go. I'm new to the world of Microsoft servers and their performance with SQL, so the show stopper I'm concerned about is related performance or connectivity. I'm sure that all will probably be OK, especially as we are moving onto something a lot more modern (and shiny!)... but experience has taught me to always be prepared for the worst! We'll have to work on some load testing for the new hardware! Many thanks again
0 Likes 0 ·
Alvin Ramard avatar image
Alvin Ramard answered
If you are converting the server to SQL2012, you also need Windows backup so you can restore the database engine back to SQL2000. You don't need those if your migrating to a new server.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I strongly agree with this comment. I prefer all my clients to do side by side upgrades when possible because you have a very simple, easy and quick rollback process if something doesn't go as expected. Also strongly agree with the recommendation of testing the whole plan with dev, uat and QA environments and doing testing to validate that your applications work as expected throughout the whole process before considering do the migration in production.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.