BTerrell Group Blog

by Kevin Yu

I ran across an interesting situation last week where I needed to restore a backup copy of a SQL 2012 database to SQL 2008 R2. You can restore databases from 2005 or 2008 to 2012 by using the backup and restore functions in SQL Server Management Studio, but not the other way around.

What I did to get work around this issue was to use the Generate Scripts… function.

This brings up a wizard that lets you choose what objects you want to include and provides you some options with how your scripts should be saved or published. Within the Advanced Scripting Options window, I chose SQL Server 2008 R2 since that was the version I needed restored. I also included Schema and data for the Types of data to script.

If the script is not too large, you can just run the script in SQL Server Management Studio without any issues. However in my case, since I chose to script the schema and data, I got an error message when trying to run the script saying that I had insufficient memory to continue the execution of the program.

To work around this issue, I used the sqlcmd utility to run my script from the command prompt.

1. Open command prompt window
2. Type sqlcmd –S server\instance –i C:\MyScriptFile.sql
3. Press Enter.

I’ve included a list of other arguments you can pass sqlcmd below:

In this situation where the database versions differ, I actually recommend upgrading the older database version to match the newer version if possible. If not, then I hope the steps I took above help you work around the compatibility issues between versions.