I am getting ready to restore a 145 gig database. What are my best options for estimating the time it will take to complete the restore. This is a one time operation on a new server, so I do not have previous metrics to compare restore times against. I am aware of enterpise manager's restore wizard's progress bar. It shows a little blue bar moving forward, which is ok. I was hoping for something more concrete. Any ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Restore Database - time estimate or progress indicator
- Thread starter TWillard
- Start date
How many CPUs in the machine? What type of CPUs? How much RAM? What types of hard drives in there (what RPMs? SCSI?)?
On my dual P4HT Xeon 3Ghz machine with 4GB of RAM and 10k RPM SCSI drives, running Windows Server 2003 and SQL Server 2000, it restores a 1GB database in 20-40 seconds from a backup on the same drive RAID.
I can't really help you in terms of offering whether or not that scales linearly on the same/similar hardware or not, or what differences in the databases will slow that down.
On my dual P4HT Xeon 3Ghz machine with 4GB of RAM and 10k RPM SCSI drives, running Windows Server 2003 and SQL Server 2000, it restores a 1GB database in 20-40 seconds from a backup on the same drive RAID.
I can't really help you in terms of offering whether or not that scales linearly on the same/similar hardware or not, or what differences in the databases will slow that down.
- Thread starter
- #3
- Thread starter
- #5
- Thread starter
- #7
mrdenny
Programmer
- May 27, 2002
- 11,595
I'd have to disagree. I have an 80 Gig database which takes 45-60 minutes to backup, but 5 hours to restore.
When you do the restore a few things will happen.
1. SQL will waist a large amount of time creating the files and writting 0s to them. This can take a couple of hours depending on the speed of your disks.
2. SQL will then start loading the data into the tables.
When you fire off the restore add the stats=1 flag to the restore. This will give you an output to the query analyzer window for every 1% of the restore that has completed. Time the amount of time beteen when 1% shows up and 2% shows up. Then take the number of minutes or seconds and multiply by 99 (or 100 to make the math easier).
Restoring will depend mostly on the drive speed.
With a database that large I would recommend detaching the database from the old server, and attaching it to the new server. This will get you back up and running much faster.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
When you do the restore a few things will happen.
1. SQL will waist a large amount of time creating the files and writting 0s to them. This can take a couple of hours depending on the speed of your disks.
2. SQL will then start loading the data into the tables.
When you fire off the restore add the stats=1 flag to the restore. This will give you an output to the query analyzer window for every 1% of the restore that has completed. Time the amount of time beteen when 1% shows up and 2% shows up. Then take the number of minutes or seconds and multiply by 99 (or 100 to make the math easier).
Restoring will depend mostly on the drive speed.
With a database that large I would recommend detaching the database from the old server, and attaching it to the new server. This will get you back up and running much faster.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question