Restore from 2008 R2 TRM to 2005 SP2 - is it possible?

Question

Post reply

Restore from 2008 R2 TRM to 2005 SP2 - is it possible?

kay carey

SSC Rookie

Points: 25
More actions
November 22, 2010 at 2:15 pm

#229032

Hello,
Is it possible to retore a SQL 2008 R2 RTM database to SQL 2005 SP2? If so - how?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

Not that I know of.

However, you should be able to use the Import or Export wizard to replicate the data on the 2005 server.

GSquared SSC Guru Points: 260824 More actions · Answer 2

You can't restore databases to prior versions of SQL Server. Can't detach-reattach back versions either, for the same reasons.

Import/Export will work. Either with a wizard or with SSIS, or using something like Red Gate's SQL Compare and Data Compare.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Hi

Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

Reverse is not possable

Thanks

Parthi

Thanks
Parthi

Paul White SSC Guru Points: 150467 More actions · Answer 4

kay carey (11/22/2010)
Is it possible to retore a SQL 2008 R2 RTM database to SQL 2005 SP2?

No. Why do you need to do this? More information gets you a better answer.

Replication is another option not mentioned so far.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

parthi-1705 (11/22/2010)
Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

If you're talking about compatibility level, it makes no difference. A database on SQL 2008 cannot be restored to SQL 2005 no matter if the compat level is set to 100, 90 or 80.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

GilaMonster (11/22/2010)
If you're talking about compatibility level, it makes no difference. A database on SQL 2008 cannot be restored to SQL 2005 no matter if the compat level is set to 100, 90 or 80.

So can we set compatibility level as 100 in sql server 2005 and work

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You can't set compatibility mode to 100 in SQL 2005. The highest is 90. 100 requires SQL Server 2008.

If you meant something else, please be clear.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 8

GilaMonster (11/23/2010)
You can't set compatibility mode to 100 in SQL 2005. The highest is 90. 100 requires SQL Server 2008.
If you meant something else, please be clear.

Thats why i have stated version wise compatibility to show that we cant use the compatibility which the sql server 2008 has cant be restored in sql server 2005.

To show the compatibility of each version i have stated there, so as you said compatibility 100 cant be set in 2005 it is clear that 2008 cant be stored in 2005 thats what i tried to say

Thanks

Parthi

Thanks
Parthi

Ben Miller SSCrazy Points: 2995 More actions · Answer 9

As has been said, you cannot restore a database backup from a later version to an earlier version period.

When you bring a database forward to a later version from an earlier version, there are physical changes made to it, and a version number assigned to the version of database it is now. This is what precludes moving a database from later to earlier versions. The earlier versions would have no idea what to do with the physical changes that were made, so it complains. It also has a version check in it that would check the version of the database and not let you attach/restore databases that would conflict.

All the answers have led you to the path that I stated, I thought I would just offer you a why, so that you understand it is not about the compatibility, or other property, it is just not allowed in the engine.

Ben Miller
Microsoft Certified Master: SQL Server, SQL MVP
@DBAduck - http://dbaduck.com

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 10

dbaduck (11/23/2010)
All the answers have led you to the path that I stated, I thought I would just offer you a why, so that you understand it is not about the compatibility, or other property, it is just not allowed in the engine.

Then how does the engine knows which version ,compatibility,property has been set?

Thanks

Parthi

Thanks
Parthi

Ben Miller SSCrazy Points: 2995 More actions · Answer 11

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to.

You can see the version of the database by:

USE master;

GO

SELECT DatabaseProperty ('databasename', 'version');

GO

ref: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/04/26/what-s-the-difference-between-database-version-and-database-compatibility-level.aspx

Ben Miller
Microsoft Certified Master: SQL Server, SQL MVP
@DBAduck - http://dbaduck.com