Copying Databases between Different Versions of SQL Server

[WWGD]

Hi All,
I am trying to move databases ( together with associated data objects) between an instance in SQL Server
Express (2016) and an instance living in SQL Server Developer 2017. I know that, between instances of the same version I can : 1) Copy the script in one instance and run it in the instance I want to move the database to 2) Backup in one instance and restore in the destination distance 3) Detach a database and resore it in a new instance.
Now: ** Will any of these methods work when moving to a new version? Is there anything else that would work?
Thanks.

 

[jedishrfu]

I've not used any of these solutions and can't say whether they'll work for sure. In any event, I thought they may lead to a solution for your issue.

There are answers to similar questions here:

https://technet.microsoft.com/en-us/library/ms186390(v=sql.105).aspx

and here's another view of how to do it:

https://solutioncenter.apexsql.com/restore-sql-server-backup-to-a-newer-version-of-sql-server/

Usually the backup tool makes a database copy that can be restored on different versions and even different vendors.

Here's a vendor based solution:

https://docs.microsoft.com/en-us/sql/ssma/db2/migrating-db2-databases-to-sql-server-db2tosql

 

[JorisL]

You should be able to migrate from 2016 to 2017.
From 2017 to 2017 is likely not possible when using backups.

I know for a fact that this is the case for 2016 -> 2014 and 2014 -> 2012.

If it's for use by third parties I'd go for something like 2014.
If it's for personal (or internal use in a company) you can use the version you like.

 

[WWGD]

You should be able to migrate from 2016 to 2017.
From 2017 to 2017 is likely not possible when using backups.

I know for a fact that this is the case for 2016 -> 2014 and 2014 -> 2012.

If it's for use by third parties I'd go for something like 2014.
If it's for personal (or internal use in a company) you can use the version you like.

Thanks, but it is not just a difference in years, it is also a different in version: 2016 is Explorer, while 2017 is the Developer version.

 

[rootone]

A lengthy but reliable method is copy the original database into the form of pure text records (ASCII).
Then read those text files into the new system.
This should work even if the original DB system is completely unrelated to the new one.
It has an advantage too that you are automatically getting a full backup of the data.

 

[newjerseyrunner]

Normally what I do is mysqldump, followed by an import. It doesn't take very long and you can even pipe them together if you're familiar with the command line.

 

[WWGD]

Normally what I do is mysqldump, followed by an import. It doesn't take very long and you can even pipe them together if you're familiar with the command line.

Thanks, but I have SQL Server ( Explorer, Developer ) at the two ends. Would that still work?

 

[WWGD]

Sorry, I forgot to ask whether migrating databases will also transfer objects: indexes, triggers, logins, etc. into new database.

 

[rootone]

Everything existing in the original database should be able to be dumped in some form that the new database can use.
Usually you don't bother with indexes as those are automatically rebuilt when the data is imported to the new system.
If there are significant methodology differences between the two systems, it might be necessary to translate some data components so that the new system can make sense of it.
If that's the case (generally unlikely), you'll need to have a program written which does the job of translating those difficult bits.