Switch to new database

What to do to upgrade your database or to move database from one hosting provider to new one.

Morning dear visitor

Sunday morning isn't always lazy. Today I am at work :D In company we are preparing a database upgrade with switch to Azure. Big step into future :)

I wanted to write about how to prepare this process. First thing - new database must have the same data as yours. There is a few options you can use:

1. Backup database and import backup file to new database.
2. Generate a schema script, generate data insert script and use it in new db
3. Use tools to compare and sync databases - like RedGate Data Compare for SQL compare There are probably other ways but I'll focus on all considered by me.

After my research I know that each solution have advantages and diadvantages. And probably in your project you can't use all of them. In my case first two weren't possible. But let's discuss the pros and cons first.

Backup database - simplest and easiest way of having two databases the same. You need to generate .bak file and import file in destination database. Then connection string switch and release. Sounds easy, but there are many limitations so this option isn't popular. For example let's imagine you want to upgrade db to new version. You likely be disappointed but sometimes old backups won't work with new databases. Another problem if your database is not empty and you want to sync missing records. Lot's of conflicts and not much to do to fix it. Another, important issue is: backup daone, you are importing and new records came. It's hard to compare and sync.

Second solution is scripts. For new tables with few records it looks easy. But when you have hundreds of tables with thousands of records generating scripts is a pain. It will take ages to generate and ti import everything. And in case of conflicts it can be impossible to review changes and select correct solutions. And also the same with syncing. You can't easily find differences to fix.

Third solution, pretty simple. There are tools which will help you with move database process. Disadvantage is a cost of tools. And sometimes being patient is needed as syncing can take ages! But you don't to worry about schema, about data. You can move all or pick what you want to have. Your data has changed in the middle of deploy? No problem, you can sync differences running compare again! As an example how it works I'll describe how to do all with Redgate Software. All you need to do is to compare database schemas and data. Firstly we can connect to old and new databases. Having connection the tool will compare schemas and shows us a friendly table with differences. We can pick what we need to sync and perform sync. To perform this action we can generate a script or directly run deploy in Redgate compare. Perfect.

In my case backup was not possible because I had to move db from SQL 200 to 2016 :D Trust me, 16 years makes the difference. :) I gave up with scripts waiting few hours and seeing 30% done ... Only one way was an external tool. For me it worked well (quite well as the latest version don't support ... SQL 2000 :D )