A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine?

Question

A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine?

haiao2000

Hall of Fame

Points: 3734
More actions
April 22, 2013 at 2:25 pm

#292427

Quick question experts!
A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

haiao2000 (4/22/2013)
Quick question experts!
A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine?

Sure no problem. It cannot however go the other direction. You can always restore a back from an older version to a newer version. However, in compatibility mode, the newer version will not be able to be restored on the older version.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

haiao2000 Hall of Fame Points: 3734 More actions · Answer 2

Thanks Sean,

So after restored on a SQL 2008 R2 engine, the database being upgrated to 2008 R2 or is it still considered sort of SQL 2005 database running on compatibility mode on 2008 R2?

Thanks!

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

haiao2000 (4/22/2013)
Thanks Sean,
So after restored on a SQL 2008 R2 engine, the database being upgrated to 2008 R2 or is it still considered sort of SQL 2005 database running on compatibility mode on 2008 R2?
Thanks!

Been awhile since I restored an older version to a newer version so I can't remember for sure. IIRC it will be in the same compatibility mode that is was restored from. It is certainly easy enough to check the compatibility after it is loaded.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

haiao2000 (4/22/2013)
Thanks Sean,
So after restored on a SQL 2008 R2 engine, the database being upgrated to 2008 R2 or is it still considered sort of SQL 2005 database running on compatibility mode on 2008 R2?

It will be a SQL 2008 R2 database with compatibility set to 90 (SQL 2005)

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

haiao2000 Hall of Fame Points: 3734 More actions · Answer 5

Mucho gracia guys. I think it means Thank you guys =)