World is now on Opti ID! Learn more

Fredrik Söderberg
Dec 21, 2010
  10602
(0 votes)

SQL backward compatibility issues

Compatibility
Have you ever had backward compatibility issues with different SQL Server database versions? I.e. you can't use a database from a newer SQL version in an older or you're not able to restore a backup from a newer SQL version to an older. An example of an error message you get when trying to use a database from SQL Server 2008 R2 in 2008:

"The database "dbDemo" cannot be opened because it is version 661. The server supports version 655 and earlier."

A solution is to upgrade the database. If that isn't an option, then what to do?

 

Try Scripting!
Scripting a database is just another way of taking a copy/backup of a whole or part of a database. The outcome is a sql-script which is backward compatible. I.e. if you script a database you are able to use it in older SQL Server versions. For instance, if you have a database in SQL Server 2008 R2 you can script it and then use it in SQL Server 2008 R2, 2008, 2005 or 2000. When you run the script in the new database environment, a new database is created or new tables etc.

 

How do I script a database?
This is how you do it in SQL Server 2008 R2 so that you get a sql-script which is backward compatible with SQL Server 2008. The script contains the database structure and the content.

1. Open SQL Management Studio and right-click the database to script. Choose Tasks -> Generate Scripts...

1

 

2. Click Next on the Introduction.

2

 

3. Because we want the whole database to be copied we choose to script entire database and all database objects:

 3

 

4. Save the script to a specific location and to a single file and then press the Advanced button:

4

 

5. In the Advanced Scripting Options dialog there's a few options that are important to change:

5

* Script for Server Version - you can choose which SQL Server version to script to (make compatible with). In this case I choose SQL Server 2008.

* Script USE DATABASE:
- False when the script is expected to be used in a different database (our case)
- True to make sure that database objects are created in the correct database

* Types of data to script - Schema and data is our choice. If we don't choose data we will just get the database structure, i.e. the tables and schemas etc.

There are many options. For an explanation of each go to Generate and Publish Scripts Wizard (Advanced Scripting Options Page)

 

6. Review your selections. Click Next to run.

6

 

7. You'll get a report on the result. If the result is successful you'll find the script.sql file in the location you chose in a previous step (image 4).

7

 

8. Execute the new script in SQL Server 2008 Management Studio. A new database is created with the tables, content etc.

 

SQL Server 2008 and 2005 scripting?
It's possible to create a script with structure and content in SQL Server 2008. Here's an example.

Scripting is also possible in SQL Server 2005 but I couldn't find any data option. Maybe you are just able to script the structure and not the data (?).

 

Generate and Publish Scripts Wizard Help
http://msdn.microsoft.com/en-us/library/ee240853.aspx

Dec 21, 2010

Comments

Please login to comment.
Latest blogs
Troubleshooting Optimizely Shortcuts: Why PageShortcutLink Threw an Error and the Solution

As developers working with Optimizely, we often encounter unique challenges that push us to explore the platform's depths. Recently, I tackled a...

Amit Mittal | Jul 9, 2025

Commerce 14.41 delisted from Nuget feed

We have decided to delist version 14.41 of the Commerce packages as we have discovered a bug that prevents current carts to be saved into...

Shahrukh Ikhtear | Jul 8, 2025

How Optimizely SaaS CMS Isn’t Just Another Commodity

CMS platforms these days are becoming commoditised. The modelling of most systems lends itself to automation. Giving marketers more flexibility wit...

Chuhukon | Jul 4, 2025 |

How to Set Up CI/CD Pipeline for Optimizely Frontend Hosting Using GitHub Actions

As I promised in my previous blog post about getting started with Optimizely Frontend Hosting, today I’m delivering on that promise by sharing how ...

Szymon Uryga | Jul 2, 2025