Hi guys,

It seems that it is not possible to backup a database on SQL Server 2008 and
restore it back again on SQL Server 2005.
The database is originally coming from the same SQL Server 2005 and it is in
SQL 2005 compatibility format.
I just found out this post that explains my exact problem.
http://deepakrangarajan.blogspot.com...ases-from.html

Can you please confirm the problem?

How can I get out of this situation? Should I get a licence for SQL Server
2008 and install it on my already running fine SQL Server 2005 database
server? Is there any other solution?

Please advise.

Thanks,
Martin Kulov
www.kulov.net

On Tue, 9 Sep 2008 13:28:38 +0300, "Martin Kulov [MVP]"
<Martin.Kulov@community.nospam> wrote:

>It seems that it is not possible to backup a database on SQL Server 2008 and
>restore it back again on SQL Server 2005.

Correct. You can restore from an earlier version, but not a later
version.

>The database is originally coming from the same SQL Server 2005 and it is in
>SQL 2005 compatibility format.

The use of the word "format" is incorrect in this situation.
Compatibility mode controls the behavior of SQL Server, but the
internal database format is that of the release of SQL Server where it
is running.

>How can I get out of this situation? Should I get a licence for SQL Server
>2008 and install it on my already running fine SQL Server 2005 database
>server? Is there any other solution?

Upgrading to SQL Server 2008 seems like an extreme step, and one that
should not be taken without considerable planning and testing.

If you have data on a SQL Server 2008 system that you need to move to
a SQL Server 2005 system you have to do it in two stages. First,
script the object definitions on 2008 and run the script in a new
database on 2005. Second, move the data. That can be done using BCP,
SSIS, linked server with INSERT/SELECT, etc.

Roy Harvey
Beacon Falls, CT

"Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
news:vnncc45vmsh9i254pt2tm5tn69a5l57m1e@4ax.com...
> On Tue, 9 Sep 2008 13:28:38 +0300, "Martin Kulov [MVP]"
> <Martin.Kulov@community.nospam> wrote:
>
>>How can I get out of this situation? Should I get a licence for SQL Server
>>2008 and install it on my already running fine SQL Server 2005 database
>>server? Is there any other solution?
>
> Upgrading to SQL Server 2008 seems like an extreme step, and one that
> should not be taken without considerable planning and testing.
>

Thanks Roy,

I must admit that I am not SQL zealot but when I get the message from MS
that 2005 and 2008 are "fully" compatible, I would expect that I should not
have such a major issue. I cannot take that moving to SQL Server 2008 is an
extreme step especially in the above mentioned message.
IMHO, a way to "Save as in 2005 backup format" option should be available,
because the operation of moving a database from 2005 to 2008 now is an
irreversible process.

Thanks,
Martin [VSTS MVP]

> I must admit that I am not SQL zealot but when I get the message from MS that 2005 and 2008 are
> "fully" compatible

Just out of curiosity, where did you read/hear that? IMO, such claims should be avoided since we all
know that new versions of any type of complex software do have potential compatibility issues. If
you can post the URL or similar we can report this so that the phrasing can possibly be altered.


> IMHO, a way to "Save as in 2005 backup format" option should be available, because the operation
> of moving a database from 2005 to 2008 now is an irreversible process.

Did you do a .connect on this?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Martin Kulov [MVP]" <Martin.Kulov@community.nospam> wrote in message
news:uAlKL30EJHA.3840@TK2MSFTNGP05.phx.gbl...
> "Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
> news:vnncc45vmsh9i254pt2tm5tn69a5l57m1e@4ax.com...
>> On Tue, 9 Sep 2008 13:28:38 +0300, "Martin Kulov [MVP]"
>> <Martin.Kulov@community.nospam> wrote:
>>
>>>How can I get out of this situation? Should I get a licence for SQL Server
>>>2008 and install it on my already running fine SQL Server 2005 database
>>>server? Is there any other solution?
>>
>> Upgrading to SQL Server 2008 seems like an extreme step, and one that
>> should not be taken without considerable planning and testing.
>>
>
> Thanks Roy,
>
> I must admit that I am not SQL zealot but when I get the message from MS that 2005 and 2008 are
> "fully" compatible, I would expect that I should not have such a major issue. I cannot take that
> moving to SQL Server 2008 is an extreme step especially in the above mentioned message.
> IMHO, a way to "Save as in 2005 backup format" option should be available, because the operation
> of moving a database from 2005 to 2008 now is an irreversible process.
>
> Thanks,
> Martin [VSTS MVP]

On Wed, 10 Sep 2008 17:01:51 +0300, "Martin Kulov [MVP]"
<Martin.Kulov@community.nospam> wrote:

>IMHO, a way to "Save as in 2005 backup format" option should be available,
>because the operation of moving a database from 2005 to 2008 now is an
>irreversible process.

I could have used that sort of thing a few times myself. Every new
release of SQL Server has been a one way upgrade, and I don't ever
expect that to change. So, we have to plan for it.

When the internal structure of the database files changes it would be
quite a challenge for them to do a reverse migration. While in your
case there has been no use of features that would make that
impossible, even a single column using a new datatype would blow it
right out of the water.

Roy Harvey
Beacon Falls, CT