What does a restore do? What process does it follow?

Question

What does a restore do? What process does it follow?

Crispin Proctor

SSCoach

Points: 18161
More actions
August 2, 2005 at 7:41 am

#363360

Greetings all,

Despite the obvious answer, I am wondering if anyone knows or has a nice article about what process SQL follows to do a restore.
I have just been watching the restore of a 11GB DB for the last 3 hours. (Still only 14% complete)
For the first 2:30 minutes, it sat there “doing nothing”. If was hammering the disks but that is all. CPU low, Disk IO through the roof and memory low.
After a few seconds, it had created the 110GB file and 10GB log file.
What took the rest of the time? Stamping the file with place holders or something along those lines?
The backup is sitting on a NAS so I accept that a lot of the time is take away by the network bandwidth.
Only after 2:45 minutes did it say 1% complete. Now, it’s going at the rate of about 1% per 5 minutes.

Anyone got some article, knowledge? Inside SQL server 2000 has no technical info (That I can find)
I need some reading while I wait

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I'm under the impression that a lot of the backup time is SQL server finding/creating/reserving contiguous harddrive space;

once it has the contigous space it goes pretty fast putting the data into that space. the only proof I have is that I use sysinternals Contig.exe all the time...and my mdf file and ldf files are never fragmented.

i may be wrong, but that is the behavior I seem to see.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jason Hall Hall of Fame Points: 3906 More actions · Answer 2

If you are doing a restore and re-initializing the data files (using a with move statement) the first thing the restore does is initialize the data files (marking extents and finding the necessary space). Once the files are initialized the restore starts writing data into the files.

This is one huge advantage of SQL 2005 is that I believe during a restore the files get initialized instantly. Can anyone confirm this? If so, does this mean that there will be more file fragmentation?

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

I have to disagree with that.

When restoring the MDF file are near instantly created.

The only thing I can think of is that it creates the file, in my case 110GB, and stamps it with something. Once that is complete (Maybe as a verification the area can be written to) it starts writing the actual data.

During the restore yesterday I was watching the 3 drives.

1 Backup

2 LDF

3 MDF

Backup went high for a few seconds, then MDF was busy for a couple hours, Then backup and MDF, then LDF for a while (COmmiting incomplete transactions)

What I am curious about is what was MDF doing for a couple hours while the other two were idle?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!