How to migrate the data from one database to another?

Category: sql server migration

Question

ComptonAlvaro on Mon, 13 Jun 2016 18:56:51


I have two databases, the original database that is in 2014 version and a new one that is in 2016 version, that I have created as new.

Then I have created a script with the schema from the first database and I run it in the new one. I have no problems.

But now I don't know hot to migrate the data, because I have tried to create a scritp from the first database, not only the schema, but the data too. But it give me an error in a table and the script stops. Perhaps the database it is very big to use scripts, because it has a size of 900MB and use filestream.

I only want to copy the data from the first database to the new one.

Thanks.

Replies

Olaf Helper on Mon, 13 Jun 2016 19:05:01


Hello,

Why that complicated, why not simply backup your databases on your old SQL Server and restore it on your new one, it's much easier & faster?

Copy Databases with Backup and Restore

Back Up and Restore of SQL Server Databases

ComptonAlvaro on Mon, 13 Jun 2016 19:13:14


Well, I know that when I create a new database, it can be version of sql server 2014 o 2016, so I don't know if when I backup and restore a database that I have created as 2014 version, when I restore in Sql Server 2016, if it still is 2014 version, because I want to use the new features of Sql Server 2016.

This is my second doubt, the features of Sql Server 2016 no matter the version of database? If no matter the version of database to be able to use the new features, why a database has a version?

So my idea was to create a new database, from zero, as 2016 version, so in this way I am sure that I am using the last version of both, databse and sql server engine.

Thanks.

Olaf Helper on Mon, 13 Jun 2016 19:21:41


Yes, when you restore a SQL Server 2014 database to SQL Server 2016 the "Compatibility Level" of the database will stay on level 120 = SQL Server 2014.

But you can change it on any time forward to level 130 = SQL Server 2016 and also always back to 120, without any issue.

See ALTER DATABASE Compatibility Level (Transact-SQL)

So no need to worry about backup & restore procedure.