Best way to migrate data to new db?

Question

User1655654435 posted

Hi, I have an data that I need to migrate into a new database. The two databases have a slightly different structure(table relationships, some properties have been removied some added).

I'm at a loss to how i'm going to do it because the data is quite important. I've tried just opening the old database in sql object explorer and copy the rows, which did work for one table but not the rest because they got dependent relationships. 

Mostly it's just two tables i need to copy. 1 is a TestResult table and the other is the Person table, linking those together (one Person can have many TestsResults).

I think the best way to do it would be to do it in code. So if I could just make a datacontext that had the same datamodels as the old one, I could just directly copy it into the new one making the changes as i put the data in the new. something like this:

var testResult = _oldContext.TestResult.Include(p => p.Person).ToList();

foreach(var item in TestResult)
{
    var newTestResult = new newTestResult { Title = item.Title, ....etc }
    _newContext.NewTestResult.Update(newTestResult);
}
_newContext.SaveChanges();

Allthough the names of the tables would be the same for the old and new tables, so I couldn't just make a variable to hold the data like that?

Maybe i'm just not thinking straight at the moment. Any help would be much apprechiated!

Answer 1

User753101303 posted

Hi,

Sql Server? I would do a backup/restore to have a copy of the source daabase and then I would do changes (either using a script or maybe through a database project). See https://docs.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-2017

Answer 2

User1120430333 posted

Personally, I would use a database administration tool, and there would be no why that EF would be involved. 

Answer 3

User283571144 posted

Hi bluMarmalade,

I think the best way to do it would be to do it in code. So if I could just make a datacontext that had the same datamodels as the old one, I could just directly copy it into the new one making the changes as i put the data in the new. something like this:

As far as I know,  EF migration will not migrate the data into new database, it will just migrate the structure.

In my opinion,  the easily way to acheive migrate data is using SQL Server Management Studio's Generate Scripts.

By using this, the sql server will generate the sql scripts with data and sechma. 

Then we could directly insert all the data and sechma into new database.

More detials, you could refer to below article.

https://www.mssqltips.com/sqlservertip/2500/sql-server-2008-r2-generate-scripts-wizard-with-database-schema-and-data/ 

Best Regards,

Brando