CODEWITHSUNDEEP
sqlsql-server-2008
Richard S.
Richard S.

Reputation: 743

Migration script methods/procedure

Looking for some suggestions on my data/schema migration. Here is what I plan to do. using sql 2008

  1. Back up current databases
  2. Restore as "_old" (to be used for data transfer later)
  3. run my scripting changes to the target DB's
  4. then, Run my data scripts transferrring data from the "_old" db's to the now new database.
  5. verify everything is working (websites, applications, etc..)
  6. delete the "_old" databases
  7. run back up on new "changed" databases.

This is my first migration and I want some guidance if I am missing anything or if there is a better way to do this. Thanks for the help..

Upvotes: 2

Views: 187

Answers (2)

Tausif Khan
Tausif Khan

Reputation: 2278

You must be very perfect for your step 4. and make sure you do it through transactions. You should keep in mind the each and every step of failure and target that.

And regarding step 6. do not delete your _old. Keep it in a safe place for future use if required.

Upvotes: 1

kevinskio
kevinskio

Reputation: 4551

  • I practised the migration I did on a development stack a number of times so that I could be sure how long it would take and work out any problems with the scripts.
  • Verify how long you have to do the migration with how long it takes. Is there an adequate margin of error?
  • It would be a good idea to get some users or other staff to verify that the new application is 'working'. You are not the best person to test your own work.
  • I would not delete the _old database just to be sure. I have found issues with the migration months afterwards that required the old data to resolve.
  • Automate as much possible by using master scripts that call other scripts.
  • A worst case scenario assumes your scripts will fail during the migration. Build logging and progress points into your scripts so you might be able to restart mid process.
  • Take some performance measurements of the old database so you can show how the new database is, hopefully, improved

Upvotes: 0

Related Questions