Try our conversational search powered by Generative AI!

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

SQL backward compatibility issues

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...



2. Click Next on the Introduction.



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



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



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


* 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.

- 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.



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).



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

Dec 21, 2010


Please login to comment.
Latest blogs
Optimizely Opal... what it does actually do?

At Opticon 2023, Optimizely announced its first AI product Opal. AI is definitely the new tech buzzword in 2024 and with promises that AI will be...

Jon Jones | Feb 25, 2024 | Syndicated blog

How to add more Content Area Context Menu Item in Optimizely CMS 12

Hey folks, today I will share something related to Context Menu customization in the Content Area of Optimizely CMS. As you know, the content area ...

Binh Nguyen | Feb 25, 2024

Developer meetups in Stockholm & Helsinki

It's time for developer meetups! Next month we will be in Stockholm and Helsinki. Join us for getting the latest updates from Optimizely, be inspir...

Magnus Kjellander | Feb 23, 2024

Roll Your Own Security Headers

Proper security headers are a must for your Optimizely driven website. There are a variety of tools out there that will help with this, but when...

Ethan Schofer | Feb 21, 2024