Restoring MSSQL 2008R2 to MSSQL 2008

Question

Restoring MSSQL 2008R2 to MSSQL 2008

mike-1098131

Grasshopper

Points: 15
More actions
May 23, 2011 at 1:46 am

#237892

Hi
Am quite a newcommer to MS SQL Management studio.
I was trying to restore a database created in MSSQL 2008R2 on my machine to a remote machine running MSSQL 2008.
Now I know this isn't possible but I have been trying to find a way to acheive this, without luck.
Is there any way to do this?
Surely MSSQL 2008R2 must be backward compatible.
Any info would be gratefully received

Viewing 7 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

There is no way to restore a higher version DB to a lower version of SQL. This is true no matter what versions you're dealing with.

The database file structures may differ, the database internal version number differs and each version of SQL has a maximum database version that it understands.

Script all objects, export all data, recreate on the lower version.

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

BrainDonor SSCoach Points: 19269 More actions · Answer 2

Now I know this isn't possible....

Then you've answered your own question - it isn't possible. R2 is a later version than 2008 and you can never 'downgrade' a database. The only option I can think of is to script everything out of the 2008R2 database to create a new database in 2008.

BrainDonor

Steve Hall
Linkedin
Blog Site

mike-1098131 Grasshopper Points: 15 More actions · Answer 3

Hi Gail

Thanks for that quick response.

What I find supprising is that there is no way in 2008R2 to 'Save As' type of means to create the backup for a previous version of MSSQL.

Regards

BrainDonor SSCoach Points: 19269 More actions · Answer 4

Didn't mean to sound like I was just copying you Gail - five minutes between pressing the 'Reply+' button and the 'Post Reply' button can make a world of difference! Damn these people that expect me to work while in the office.

BrainDonor.

Steve Hall
Linkedin
Blog Site

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

mike-1098131 (5/23/2011)
What I find supprising is that there is no way in 2008R2 to 'Save As' type of means to create the backup for a previous version of MSSQL.

Do you have any idea how complex something like that would be? Let's take the situation of a 1TB database that's using unicode compression (a 2008 R2 only feature). How would you 'save as' SQL 2008? A backup does interpret or investigate the data pages that it backs up. It reads them from the data file, writes them to the backup destination, nothing more.

It's incredibly complicated to do. Often between versions the system tables change, sometimes the allocation structures change, page headers change, data storage changes (eg compression).

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

mike-1098131 Grasshopper Points: 15 More actions · Answer 6

Hi Gail

In answer to your comment 'do I know how complicated it would be' then answer would be, I do now 🙂

Thanks for the info. It looks like I will have to downgrade to 2008.

Again thanks

Mike