How to migrate or downgrade a SQL Server database to a lower version

Posted written by Paul Seal on April 21, 2017 SQL

What is this post about?

This post shows you how you can migrate or downgrade a SQL Server database from one version to a lower or earlier one. E.g. 2016 to 2014, 2014 to 2012, 2012 to 2008 etc

If you're looking to learn how to upgrade or migrate to SQL Server 2016 then you should definitely check out this course on Pluralsight.

Follow these steps:

  1. Open SQL Server Management Studio
  2. Right click on the database you are working with.
  3. Choose Tasks > Generate Scripts
  4. You will be presented with a wizard screen, on the introduction you can click next.
  5. Choose whether to script the entire database and objects or to select them individually. I like to leave it as the first option, everything. Click next
  6. In the output type, choose save scripts to a specific location
    Save to file
  7. Click on advanced
    Script for Server Version
    Choose the version you want, mine is 2012
    Types of data to script - Schema and data
    Leave the other settings as they were.
  8. Click on OK
  9. Files to generate - Single file
    File name - give the script a name with the extension .sql
    Save as Unicode text
  10. Click next
  11. Open out options using the plus sign
  12. Check the settings are all ok in the summary - you are mainly looking at the version and the types of data to script
  13. Click next
  14. Wait for it to run, you should see green ticks all the way down if it was successful.
  15. Click on Finish
  16. Then go to the location where you saved the script.
  17. Open the script
  18. Make sure the path of the files for the .mdf and log.ldf files is correct.
  19. Run the script
  20. You may get some errors like this:
  21. Cannot add the Principal 'CodeShare' because it does not exist or you do not have permission
    It is fine to ignore this. It's never caused me an issue.

That's it!

You're all done. You should be able to start using the database now.