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.

 

 

Paul Seal

Umbraco MVP and .NET Web Developer from Derby (UK) who specialises in building Content Management System (CMS) websites using MVC with Umbraco as a framework. Paul is passionate about web development and programming as a whole. Apart from when he's with his wife and son, if he's not writing code, he's thinking about it or listening to a podcast about it.

Proudly sponsored by

Moriyama

  • Moriyama build, support and deploy Umbraco, Azure and ASP.NET websites and applications.
AppVeyor

  • CI/CD service for Windows, Linux and macOS
  • Build, test, deploy your apps faster, on any platform.
stkrs

  • Custom stickers for startups, artists and brands.
  • Bespoke easy-apply, removable, custom brand stickers printed in the UK.
elmah.io

  • elmah.io is the easy error logging and uptime monitoring service for .NET.
  • Take back control of your errors with support for all .NET web and logging frameworks.
uSync Complete

  • uSync.Complete gives you all the uSync packages, allowing you to completely control how your Umbraco settings, content and media is stored, transferred and managed across all your Umbraco Installations.