Restore

Question

Restore

Viewing 14 posts - 16 through 28 (of 28 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Great question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Interesting, that the documentation (or at least the page referenced in this question) never once mentions filegroup restore. I thought that filegroup restore was possible, but (since DBA is not my primary task) decided to check BOL first. I came across the exact page referenced in the answer, noticed no mention of restoring at the filegroup level, so decided to untick this option.

Ah well. Points lost, but lesson learned - now I hope I'll never have the opportunity to reinforce this knowledge in my job. 😉

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

Hugo Kornelis (1/19/2011)
Interesting, that the documentation (or at least the page referenced in this question) never once mentions filegroup restore.

http://msdn.microsoft.com/en-us/library/ms186858%28v=sql.90%29.aspx

About 1/3 of the way down:

SQL Server 2005 supports a variety of restore scenarios:
* Complete database restore
Restores the entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups). For more information, see Performing a Complete Database Restore (Simple Recovery Model) or Performing a Complete Database Restore (Full Recovery Model).
* File restore
Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored. For more information, see Performing File Restores (Full Recovery Model) and Performing File Restores (Simple Recovery Model).
* Page restore
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. For more information, see Performing Page Restores.

Also http://msdn.microsoft.com/en-us/library/ms178615%28v=sql.90%29.aspx under the heading "<file_or_filegroup_or_page>"

It's not particularly clear or obvious.

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

Thanks, Gail.

So apparently, the documentation is not incomplete, it just hides it's completeness very well 😉

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Hugo Kornelis (1/19/2011)
Thanks, Gail.
So apparently, the documentation is not incomplete, it just hides it's completeness very well 😉

I think more that it's poorly written. I miss the "submit a note" item at the top right for the online BOL

Hit The Buffers SSC Rookie Points: 44 More actions · Answer 6

Bit pedantic I know, but Gail is quoting 2005 documentation and the question was 2008 R2 specific. The referenced documentation initially states database, data file and data page as supported data restore levels (which is how I missed file group :crying:) - however, in the 'Restore Scenarios' section, file groups are mentioned under 'Piecemeal restore'. Serves me right for not reading the whole page before answering the question 😀

SanDroid SSChampion Points: 10068 More actions · Answer 7

Steve Jones - SSC Editor (1/19/2011)
I miss the "submit a note" item at the top right for the online BOL

So do I. Unfortunate that a large percentage of the submissions where hate mail or links to sites selling p#n%$ enlargement.

SanDroid SSChampion Points: 10068 More actions · Answer 8

GilaMonster (1/17/2011)
SanDroid (1/17/2011)
Not certain who is learning.
Restoring a group of pages can restore an extent.
There is no RESTORE DATABASE ... EXTENT command. There is a RESTORE DATABASE ... FILEGROUP, a RESTORE DATABASE ... FILE and a RESTORE DATABASE ... PAGE

That is understood an not relevant in the least to my point.

The question was the types of restore supported, not the restore commands supported. Wording is everyting in a question and it's answer.

When you have "database extent corrupted or unavailable" errors you use RESTORE PAGE to resolve it.

I guess the fact that I had to do this to a database altered my perception of what would be the correct answer.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

SanDroid (1/20/2011)
That is understood an not relevant in the least to my point.
The question was the types of restore supported, not the restore commands supported. Wording is everyting in a question and it's answer.
When you have "database extent corrupted or unavailable" errors you use RESTORE PAGE to resolve it.
I guess the fact that I had to do this to a database altered my perception of what would be the correct answer.

If you took that wording to mean how can I extend things to restore an extent, I think you are misreading the question yourself. You are overthinking the process.

Restoring an extent is not possible. Restoring pages to rebuild an extend is possible, but that's not the same thing. Saying this is the same is playing with semantics.

UMG Developer SSChampion Points: 13482 More actions · Answer 10

UMG Developer

SSChampion

Points: 13482

January 21, 2011 at 3:40 pm

#1276841

Thanks for the question!

SanDroid SSChampion Points: 10068 More actions · Answer 11

Steve Jones - SSC Editor (1/20/2011)
SanDroid (1/20/2011)
That is understood an not relevant in the least to my point.
The question was the types of restore supported, not the restore commands supported. Wording is everyting in a question and it's answer.
When you have "database extent corrupted or unavailable" errors you use RESTORE PAGE to resolve it.
I guess the fact that I had to do this to a database altered my perception of what would be the correct answer.
If you took that wording to mean how can I extend things to restore an extent, I think you are misreading the question yourself. You are overthinking the process.
Restoring an extent is not possible. Restoring pages to rebuild an extend is possible, but that's not the same thing. Saying this is the same is playing with semantics.

My point was that resotration of an Extent is something paossible using page restores, just like piecmeal restores offer us other possibilities. This is something that does not seem to be well understood. I apreciate the fact that you do. 😀

SanjayAttray SSChampion Points: 13157 More actions · Answer 12

Tom.Thomson (1/15/2011)
Nice easy question, nice clean answer.
But why on earth is it worth 2 points?

That's because 1% of total answered still are not sure about full backup restores. I understand if some people miss file restores as they are not regular job but, people still are out there that believes FULL backup restore is not possible.

what types of restores are possible?

SQL DBA.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 13

Good question.

I don't know that we can restore single page as well.

Thanks for the question & nice discussion about the topic.

Thanks

zymos SSCommitted Points: 1960 More actions · Answer 14

Nice question and tricky topic to many, especially beginners and it is well explained.

Thanks.

URL: http://msdn.microsoft.com/en-us/library/ms186858.aspx