Piecemeal restore takes forever

Question

Piecemeal restore takes forever

Viewing 8 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

Do you know how long a full restore (non-piecemeal) of all filegroups takes?

Also, are the filegroups of the larger tables that you've partitioned "Read_Only"? If not and IIRC, all read/write filegroups will be restored with the PRIMARY filegroup during piecemeal restores.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stakes Ten Centuries Points: 1376 More actions · Answer 2

Hi Jeff, thanks for replying. I don't know how long a full restore on this particular test machine takes, but I suspect longer than 2 hours. The machine is in a seperate virtual testenvironment with old hardware and I don't have the space to restore the entire database. In another testenvironment, with better h/w, it takes about 1-2 hours.

The filegroups are all read/write. Indeed all read/write filegroups will be restored with the PRIMARY filegroup during piecemeal restores, if the database is in simple recovery mode. But it isn't, it's in full recovery mode.

From BOL:

"Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups. "

(http://msdn.microsoft.com/en-us/library/ms177425(v=sql.105).aspx)

Perry Whittle SSC Guru Points: 234065 More actions · Answer 3

stakes (7/11/2013)
The machine is in a seperate virtual testenvironment with old hardware and I don't have the space to restore the entire database. In another testenvironment, with better h/w, it takes about 1-2 hours.

can you supply more info on the virtual disks for this VM and the storage that is provisioned for the underlying data store.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

I guess I don't know the answer to your problem then. I was thinking that a full restore was takeing you 6 hours and, although a piecemeal restore bring the "guts" of the system online more quickly, it would still take about the same amount of time to ultimately peicemeal restore all of the filegroups.

Thanks for the BOL tip. I'd forgotten that peicemeal restores were handled differently for the SIMPLE recovery model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stakes Ten Centuries Points: 1376 More actions · Answer 5

Hi Perry, what info exactly do you want? I want to give it to you, but I don't think it's the virtual disk or the VM itself, because other restores on the same machine do go fast.

DominantDBA SSC Eights! Points: 862 More actions · Answer 6

Im having the same issue where I thought picemeal was going to be quicker

I am developing a DR solution and one of the things Im testing is peacemeal restore as a means to get us up and running and sadly Im finding

that it doesnt perform.

A full restore takes about 20 second on my test database but when I do a peacemeal restore it takes 20 minutes!!!

Ive also checked instatant file intialization et al and its setup correctly but it just takes ages to create the files of the filegroups

not sure what else I can try

stakes Ten Centuries Points: 1376 More actions · Answer 7

Hi DominantDBA and all others who encountered this problem. For piecemeal restores to work as fast as they are suppose to, you have to backup your filegroups seperately. That way you can restore them 'piece-by-piece' in a timely manner. This is how piecemeal restores are meant to be, if you read the MS documentation carefully. Of course, your backup strategy will become more complex by backing up your filegroups seperately and that's why MS should make piecemeal restores from full backup's run faster as they do now. At least, in my opinion.

DominantDBA SSC Eights! Points: 862 More actions · Answer 8

DominantDBA

SSC Eights!

Points: 862

September 25, 2013 at 6:55 am

#1653117

Thanks...back to the drawing board!