April 12, 2007 at 8:10 am
Hello,
I am trying to restore a database from a backup file that is approximately 176 Gb. I let the job run for over 7 hours yesterday and then canceled it. My question is how long should a restore take to complete for a database of this size? The backup file is stored on a local drive, network traffic is not an issue. The server is fairly new with latest hardware, and OS is Windows 2003. I am using SQL Server 2000.
Thank you for any input.
April 12, 2007 at 8:26 am
Just my experiences.
The rate of a restore process is in the order 0f 40 GB/hour. Normally, it is in the range between 20 - 60 GB/hour.
To check if your restore is running, you can run
SELECT * FROM master..sysprocesses
to see if IO and CPU increases with each run.
Some applications running on your server may slow down your restore.
The worst scenario is the backup file is corrupted. Normally, you should receive an error message if so.
Best luck!
April 12, 2007 at 8:34 am
That does sound long. I think SQL_ORACLE is right. I'd expect a 200GB file to be restored in 3-6 hours tops. If you have spread out the disk I/O, data, it could be quicker. I'd redo it, but also keep an eye on disk i/o and process CPU from System Monitor or Task Manager. See if counters dip after some time.
Also, is the 176GB backup file on the same drive as the data file? Is is compressed in anyway?
April 12, 2007 at 8:43 am
Yes, the 176Gb file is on the same drive as the data file. The drive size is over 600Gb. No, the file is not compressed. It is possible that the file became corrupted during the copy across the network. The same file copy failed after 10 hours last night on a gigabit connection. I'm not sure what is going on with the server.
Thanks!
April 12, 2007 at 8:51 am
Copying a backup file across internet may experience file corruption. If a backup file is corrupted, SQL Server will "feel" confused and takes a longer time than normal to return a message.
I copied a backup file across internet couple months ago. When I restored the database, I got an error. After checking the problem, I found the copying process was terminated for some reasons.
April 12, 2007 at 12:44 pm
How did you do the restore? Did you make sure to use WITH RECOVERY? If not, then the database will continue to show recovering. One of the first things I suggest doing when a db seems to be 'stuck' in recovery is to run:
RESTORE DATABASE dbname WITH RECOVERY
-SQLBill
April 13, 2007 at 3:45 am
if you are restoring via QA also include the stats parameter which will record progress for you in percentage terms
---------------------------------------------------------------------
April 13, 2007 at 7:20 am
This is my first post here. Yesterday I restored a database that was 86GB in size. It took me precisely 46:20 minutes. It was a simple restore using EM. I checked the Force Restore as I need the database replaced with older backup.
April 13, 2007 at 2:37 pm
If you are restoring the datafile to the same drive as the backup, then you are creating a huge I/O bottleneck by forcing the disk to perform both read and write operations. Copying the backup to a different disk will speed up your restore.
April 13, 2007 at 3:39 pm
"SQL Server will "feel" confused"
I've always kind of thought that SQL Server had feelings! Anyhow, I'd agree with SQL Oracle that it is possible that the file is corrupt or may have become that way during the copy. How are you going about copying the file? I've seen problems with files becomming corrupt when using different remote software programs or even with FTP (if you are using FTP, make sure you set the connection to binary). I would also second what Edogg says in regards to the I/O contention you are causing by having both files on the same disk. You may want to consider moving the backup file to another set of local disks if you can.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply