Copying Databases between Different Versions of SQL Server

WWGD

Science Advisor
Gold Member
4,857
2,147
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.
 
11,041
4,543
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
 
487
188
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

Science Advisor
Gold Member
4,857
2,147
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.
 
3,377
942
A lengthy but reliable method is copy the original database into the form of pure text records (ASCII).
Then read those text files in to 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.
 
1,510
615
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

Science Advisor
Gold Member
4,857
2,147
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

Science Advisor
Gold Member
4,857
2,147
Sorry, I forgot to ask whether migrating databases will also transfer objects: indexes, triggers, logins, etc. into new database.
 
3,377
942
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.
 

Related Threads for: Copying Databases between Different Versions of SQL Server

Replies
6
Views
705
  • Last Post
Replies
2
Views
774
Replies
8
Views
1K
Replies
1
Views
305
Replies
2
Views
651
Replies
11
Views
443
Replies
1
Views
504
  • Last Post
Replies
12
Views
913
Top