CODEWITHSUNDEEP
sql-server
Franklin Albricias
Franklin Albricias

Reputation: 223

Change SQL Server database version

I have a SQL Server 2008 version database (version number 655). If I try to create for attach it to a SQL Server 2005 or SQL Server 2000, I get an error:

This database is version 655. This server is compatible with version 612 or previous

Of course I can attach the database to SQL Server 2008 without errors. I know I can generate SQL commands for the structure, but there is a lot of data also in many tables.

If I can change the internal database version (the compatibility level is now set to SQL Server 2000), the attach process in any server version will work, as this process updates the version automatically.

Any suggestions?

EDIT: I just realized that if the update of the version number is automatic, there will be no way of doing a version change. So, I reformulate the question:

Do you know any tool that generate a "package" with data and structure that is compatible for SQL Server versions from 2000 to 2008? A tool or a Script, perhaps.

Thanks again.

Thanks in advance.

Upvotes: 4

Views: 18527

Answers (6)

morteza karimian kelishadrokhi
morteza karimian kelishadrokhi

Reputation: 91

i have same problem but there is a way for achieving to this issue:

  • attach a Database from Server 2008(or higher) to a SQL Server 2005(or 2000)

from a higher SQL version generate a Script (right click on your database > Task > Generate scripts ) then on SQL lower version create a raw database (by the same name) and run your script.

  • you have option to generate all data(or just create tables - store procdure-user-functions , ...) and then imported to SQL lower version database

I test this solution for SQL 2012 to SQL 2005. Except the new functions that developed for SQL higher version Every Schema & data generate to .sql file and then imported to lower SQL

Upvotes: 0

Bill
Bill

Reputation: 198

¿Do you know any tool that generate a "package" with data and structure that is compatible for SQL Server versions from 2000 to 2008? A tool or a Script, perhaps.

Old post. I am adding 2 suggestions for the sake of futures readers. New T-SQL features (syntax) cannot be back-ported (of course), but for simple scenarios (where every table has a primary key) you can temporarily set up and use transactional replication. You can also write a script that will BCP OUT every table's data (I suggest /native mode), perhaps using sp_MSForEachTable for that BCP command - this is essentially what replication's snapshot process does.

Upvotes: 0

thiet ke web gia re
thiet ke web gia re

Reputation: 1

No, you can not downgrade sql 2008 to sql 2005

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

It's not possible. You cannot attach/restore an SQL Server database to an earlier version of SQL Server I'm afraid. You have to do an export/import.

Upvotes: 2

marc_s
marc_s

Reputation: 754478

You cannot under any circumstances attach a database from a SQL Server 2008 server to a previous version like SQL Server 2005.

There is no way to achieve this - there's no "conversion" tool to change the database version or anything.

If you need to support SQL Server 2005, you have to have a SQL Server 2005 server at hand (maybe in a VM or something). Or then you need to use tools like Red Gate's SQL Compare and SQL Data Compare to synchronize changes between your SQL Server 2008 and your other SQL Server 2005 database.

Upvotes: 5

TomTom
TomTom

Reputation: 62093

No, sorry. Recreate your database on the outdated SQL Server. Like most software, SQL Server only supports UPGRADES, not downgrades.

Upvotes: 2

Related Questions