0

I found out SQL Server Developer Edition is now free.

I would like to use my older databases from SQL Server Express 2016 to a new instance of SQL Server Developer 2017.

I want all the data objects: indexes, triggers, etc. to be moved together with the data files.

I am trying to figure out the ways in which I can do this. I am pretty sure these would work between instances of the same version, but I am not sure if it would work if moving between different versions of SQL Server:

  1. Backup Old and Restore to New
  2. Script Old and run the script on New
  3. Detach Old and reattach it on New

Are these 3 workable? Are there any other ways?

The instances are on the same machine, and I have not yet installed SQL Server developer edition.

Migration does not need to be immediate, but at most within a week or so.

  • Is this on the same Machine? Have you already installed a new instance of SQL Server? Are you constrained by disk space? Do you have any time constraints? (does it need to be within a certain time frame?) – Sir Swears-a-lot Oct 8 '17 at 22:08
  • 1
    Keep in mind, unlike the express edition it is not free for production workloads. – eckes Oct 9 '17 at 1:23
1

The best way will be to use backup-restore - using start-dbamigration .. This will take care of migrating entire instance including all databases, logins, jobs, etc.

Make sure you use -BackupRestore switch with appropriate network location

e.g.

Start-DbaMigration -Source sql2016 -Destination sql2017 -BackupRestore -NetworkShare \nas\sql\migration

0

The 3 options you have suggested are correct. Another option is simply upgrading SQL Server in place. If this a development instance on the same machine I would consider that as an option.

Which option is best will depend on your preferences, the size of the db's and whether you need to move machines and/or whether it is important to have a fresh installation.

As the difference between 2016 & 2017 is comparatively small, you could detach, move and reattach or backup and restore. In each case you should check the compatibility level and raise is it if you wish to use new 2017 features. If you are moving to a new SQL instance logins will need to be reattached.

Yes you could also script the the data, export/import, use a wizard or a data comparison tool. However scripting comes with some risk depending upon the tools and formats you use to export and import the data (eg .csv BCP etc). The bigger and more complex the db is the less feasible or attractive this becomes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.