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:
- Open SQL Server Management Studio
- Right click on the database you are working with.
- Choose Tasks > Generate Scripts
- You will be presented with a wizard screen, on the introduction you can click next.
- 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
- In the output type, choose save scripts to a specific location
Save to file - 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. - Click on OK
- Files to generate - Single file
File name - give the script a name with the extension .sql
Save as Unicode text - Click next
- Open out options using the plus sign
- Check the settings are all ok in the summary - you are mainly looking at the version and the types of data to script
- Click next
- Wait for it to run, you should see green ticks all the way down if it was successful.
- Click on Finish
- Then go to the location where you saved the script.
- Open the script
- Make sure the path of the files for the .mdf and log.ldf files is correct.
- Run the script
- You may get some errors like this:
- 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.