After an almost catastrophic 48 hour downtime with my existing hosting provider I decided to move my business, which involved moving a number of websites and their corresponding databases to my new hosting provider.
Now, the thing that I liked about my existing provider was that they supported SQL Server 2014. My new one doesn’t (and neither do a LOT of providers for some reason), so this meant having to downgrade my databases from SQL Server 2014 to SQL Server 2012.
I’ve never done this before so my first thought was that I would change the compatibility level of each database to SQL Server 2012, backup each database, and restore them on my 2012 instance, but this doesn’t work as a database created in a later version of SQL Server cannot be restored onto an earlier version. Probably for very good reason.
So, how is it done? Well, I’ve used the Generate Scripts feature with success before so thought I would take a look at it to see if there was anything there that I could use, and, thankfully, there is.
How to Downgrade a SQL Server 2014 Database to SQL Server 2012 (or below)
Right-click on your database from within the Object Explorer window and select Tasks, then Generate Scripts.
This will bring up the Introduction window (unless you’ve done this before and hit the Do not show this page again message) from where you can safely click Next (it seemed excessive to provide a screenshot for this!)
From the Choose Objects window make sure that you choose the option that’s most suitable for your task. For me, I wanted to script the entire database so I left the default Script entire database and all database objects selected. However, if you are only interested in particular elements of your database then the Select specific database objects option is probably right for you.
Set Scripting Options
This dialogue is where things start to get er interesting. The first thing to do is decide where you want your script to end up. For me I wasn’t planning on repeating this process so I chose Save to new query window but regardless of your choice of location the step not to forget here is to click on the Advanced button as it is from this window that we get our downgrade options.
Advanced Scripting Options
Once this window is open you want to scroll all the way down to the Script for Server Version option where you’ll see a drop down list of options. As I was interested in scripting for SQL Server 2012 I chose that option but you can go right back to SQL Server 2005 depending on your requirement.
I also wanted to script both the Schema and data so I chose that option from the Types of data to script area.
Once you click OK, then Next a few more times the final script will appear in your chosen location. We’re not quite done as there are a couple of changes you need to make to the script which I’ll cover next.
Final Script Changes
The final thing to do is to alter a couple of paths in the CREATE DATABASE part of the script. By default this will show a path to your SQL Server 2014 files so all you want to do is change this path to point to your SQL Server 2012 files. For me, this meant changing MSSQL12.SQL2014 to MSSQL11.SQL2012 as I’m using the default file locations but if you’re using something different then change the path to whatever you need it to be. Also make sure that your database logins and users are available on the target server before running the script.
So, that’s it. Once you run the script it will create your database on your target server for your specified version and all will be well with the world again.