Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Copying Databases between Different Versions of SQL Server

Tags:
  1. Oct 8, 2017 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  2. jcsd
  3. Oct 9, 2017 #2

    jedishrfu

    Staff: Mentor

    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
     
  4. Oct 9, 2017 #3
    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.
     
  5. Oct 9, 2017 #4

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  6. Oct 10, 2017 #5
    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.
     
  7. Oct 10, 2017 #6
    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.
     
  8. Oct 10, 2017 #7

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, but I have SQL Server ( Explorer, Developer ) at the two ends. Would that still work?
     
  9. Oct 10, 2017 #8

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Sorry, I forgot to ask whether migrating databases will also transfer objects: indexes, triggers, logins, etc. into new database.
     
  10. Oct 11, 2017 #9
    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.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Copying Databases between Different Versions of SQL Server
Loading...