Some one help me answer this. Did my restore take 1 hour or 12 hour?
Last night, I started a
restore from disk with replace
command from a sql 2012 SSMS on a server.
The bak file is about 300 GB.
I remote into the server and started about process, but after awhile (15 min?) couldn't get back in to check.
This morning, with the help of our network guy, I went back in, and saw the process is still running.
Couple minutes later, it completed. However, here comes the confusing part.
The status line on the bottom reads: Query executed successfully. 12:27:52.
(12 is about right, i.e from I started it last night around 7 PM to now)
But the messages section showed a total different thing.
Processed 35762536 pages for database 'dbname', file 'dbname' on file 1.
Processed 9952 pages for database 'dbname', file 'dbname_log' on file 1.
RESTORE DATABASE successfully processed 35772488 pages in 3667.754 seconds (76.197 MB/sec).
--that is about only an hour
I am not sure which is correct. If they are both correct and tell different things, then I have been using the first one wrong. Another possible explanation is that my disconnect to the VM session made the command suspended during the night. That is also contrary to my understanding.
After talked to the network guy, the mystery solved.
It turned out, the LUN is sitting at ran out of space after 45 minutes into my restore process. They made some changes this morning, and my process picked up where it has left.
So, the 12 hour is the total of my SSMS command session. The clock just keeps ticking. And the 1 hour is the actual time to restore. Under normal circumstance, these two should be the same. It is interesting to note that the restore command behaves this way under the circumstance.
It sounds like storage was over-committed and although the server (and SQL Server) saw enough space - there really wasn't enough space available. Once you reached that size the VM was suspended causing SQL Server to also be suspended.
Because the server was suspended - once the storage allocation was corrected and the VM no longer suspended, everything picked up and started processing.
I think the key to this disparity might be how long it takes to build the log file. If it has a bazillion VLFs in it, it can take an extraordinary amount of time. If "instant file initialization" isn't enabled, creation of the MDF file can also take significant time.
Shifting gears a bit, copy the BAK file to a local drive may decrease the time of the actual restore but it won't help the overall time of the entire process because it's still going to take time to copy the file... usually, the same amount of time as just doing the restore straight up,
Not necessarily. Depends on the network. If you've got 10gbe from the SQL Server to the remote server, it will be pretty fast. Not as fast as a local restore, but definitely not slow.