August 2, 2020

Sql Server Migrate Database to an Older Version

By Frank Stadler Administration 0 Comments

I recently came across a situation where I had several SQL Server databases that needed to be moved to down level (ie older) version of sql server. In doing so I was looking for a solution that was out of the box and could be done using only sql server express, without the use of any enterprise licenses or expensive tools. If you try to take a database backup .bak file in a newer version of sql server and restore it in an older version of sql server it will error out, so we need to find an alternate approach. In looking for a solution I quickly became frustrated by page after page of search results saying ‘can’t be done’. I ended up pairing a couple techniques together to come up with a relatively straightforward out of the box, so I’d like to share my solution here.

I’m assuming that you already have sql server installed and running on your source and destination machines / servers. This technique can be used with sql server express, developer edition, or any of the standard or enterprise editions. I’m also assuming that you have a version of sql server management studio with access to the source database.

Part 1 – Export the data and structure into a sql file

It’s easy to script out the schema of the database, but I also wanted to preserve the data in my existing database, so I wanted a solution that would preserve both.

In sql server management studio, go to your source database that you would like to move over. Right click and select tasks –> Generate Scripts

At the introduction screen click the ‘next’ button

At the Choose Objects screen generally you will want to stick with the defaults of ‘script entire database and all database objects’ and click the ‘next’ button.

At the set scripting options screen, you can set the output file name and then click the ‘Advanced’ button.

You can read through the scripting options and set what may be appropriate for your migration. I will highlight a couple important settings. The first is ‘Script for Sever Version’ and ‘script for the database engine edition’. Set these to be the version and edition of your destination environment.

The next important setting is the option for ‘Types of data to script’ the default is ‘Schema only’ which would be fine if you want to start with empty tables. However if you’d like to preserve your data, click this drop-down and select the option ‘schema and data’

Click the ok button on the advanced options pop up window. then click the ‘next button on the set scripting options screen. Click next again at the summary screen and give it a few minutes to generate the output script.

click the finish button

Part 2 – Import the sql file using sqlcmd

What i found fairly quickly was the resulting .sql file from part1 is often fairly large depending on the size of your database. Large .sql files would often produce an out of memory error if you tried to open and run them in sql server management studio.

However the same file is fairly easy to import using the sqlcmd command line utility found as part of the sql server installation, including sql server express and developer editions.

If you need to copy the sql server file to your new server / machine you can do so. (if you don’t have remote access you may need to copy the sql file using OneDrive or usb drive).

open a command prompt. Format your sqlcmd statement as follows, adjust your statement for your destination sql server and location of your sql file.