October 22, 2007 at 5:07 am
Scenario:
Production database, total 640+GB, one mdf file and 4* 160gb add. files.
This database is backed up once a week to a 360gb backup file.
I now want to restore this backup file from a testserver ( not the server the backup is placed on)
I have tried to restore via Enterprise manager ( restore ) and in SQL Analyser ( restore )
I both situations it creates the .mdf and the first .ndf file, and then nothing seems to happen.
Is it possible to se some kind of how the restore progresses ?
any other comments / idea's of what to do
any comments appreciated
October 22, 2007 at 10:15 am
A database that large will take quite some time to do the restore. Management Studio will show the progress but there is always an initial period required before it gets going. Theis period of time may be quite long. For that size database you may want to go home and check it out in the morning. For QA make sure the command includes , STATS=5
which will show the status every 5% of the restore. You could say , STATS=2 or any other number you want
Francis
October 23, 2007 at 9:22 pm
It definitely will take time. Any idea of how long the backup takes? Does the database exist before the restore? IF not, you've got file creation time.
Check system monitor to see if you're getting disk activity on the test server.
October 24, 2007 at 12:59 pm
Thanks.
Info about the restore.
I have not been able to follow the restore progress. I decided to let the restore job run, and the result is:
Each 160gb file takes a little over 8 hours, and with 4 files in the backup this is 32+ hours to complete the restore.
October 27, 2007 at 11:53 pm
You can do the following
Backup the database from source server (backup size is 320 GB)
Copy the backup file from source to destination server. You can use xcopy or robocopy to do this, but copying through network takes long time. You can also copy this to a portable drive and take the backup to destination server
Once copied start the restore process, it will expand the backup file to individual files to 640GB (as you mentioned earlier) and will take surely 8 -10 hrs to complete and also increases you IO operation.
Regards..Vidhya Sagar
SQL-Articles
October 28, 2007 at 3:56 am
Update:
after approx 32 hours the db files were created, then i thought my db was ready, but no. It took another 34 hours where the db appeared as ( Loading ) in Enterprise Manager, so my conclusion must be that the restore process first creates the db files and thereafter restores data into them.
October 28, 2007 at 1:46 pm
Yes thats right, it will create the physical files and then start restoring the db
Regards..Vidhya Sagar
SQL-Articles
October 28, 2007 at 9:44 pm
Henrik Holm (10/28/2007)
Update:after approx 32 hours the db files were created, then i thought my db was ready, but no. It took another 34 hours where the db appeared as ( Loading ) in Enterprise Manager, so my conclusion must be that the restore process first creates the db files and thereafter restores data into them.
Why so long !!!!!!! My 275 Gig database with 1 Mdf, 1 ndf and 1 ldf takes about 2 hours
October 28, 2007 at 11:35 pm
Henrik,
My suggestion would be if you fail to restore the back up database through the sql wizard then take the LIVE database offline. Ensure before doing this, you have the very latest back up either on the disc/tapes.
Then create a folder in the respective drive of the server copy the LDF & MDF files into the respective directory and bring the database online.
This should solve the issue..
October 29, 2007 at 3:39 am
I think that restoring from a backup placed on another server via normal network is just that much slower
October 29, 2007 at 3:45 am
As already posted, the restore will first create the database files, then do the restore. You may be able to save time on the database create by exploiting 'instant file initialisation'in SQL 2005. To do this the SQL service account needs to have 'Volume Management' rights in Windows.
You should also consider the use of a 3-party backup tool multiple files for your backup. Many 3-party tools are significantly faster than SQL backup and restore, and the built-in compression of the backup file often gives disk-space savings that pay the cost of the tool.
A recent test using the Litespeed product gave a backup time of about 14 hours for a 300 GB database to a single output file, on a 4-processor box. Changing this to use 4 threads and 4 output files reduced the bacukp time to about 2 hours. Restore time using a 4-file backup was about 4 hours for the 300 GB DB.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 29, 2007 at 4:34 am
Don't forget the hardware issue! Besides restoring over a network, it could be that your DB is taking so long to restore because the hardware is either old or just not fast enough to do the restore in a more timely manner.
Several things could be forcing this process to take longer. 1) Processor. If you have only one on this test server (or even 2) and they aren't Hyper-thread capable or enabled, this could cause slowness issues. 2) You might not have enough RAM to go around for all the threads. 3) How many disk controllers do you have? If your test server only has one or two disks (especially if they're local server hard drives) then you only have 1 or 2 disk controllers trying to do all that writing. 4) Your RAID setup (if you have one) could be Mirrored which is really really slow on write operations.
Those are just some thoughts as to why this restore process is taking longer than you (or anyone else) thinks it should. Just because someone else's large db restored in 2 hours doesn't mean yours will because you both probably have different hardware. Plus, there's the restoring over the network issue that is only compounding the problem.
October 30, 2007 at 6:49 am
It is very unlikely that the CPU or RAM have very much to do during a restore. The Network is not suspect in this case, because there is a negligible amount of traffic required to get you to the file create part of the process, which is taking 8 hours for 160 GB.
If my calculations are correct, you are getting 5.5MB/s out of your disk subsystem. 160 / 8 / 3600 = .005 GB/s. This is a very low number.
I would verify the disk performance using a (free) tool called IOMeter, and see how fast it is reporting your disk subsystem with large sequential writes.
October 31, 2007 at 1:57 pm
I had an issue recently where my restores were taking excessively long. Things to check.
Obviously Virus software disabled.
Patches are up to date.
Are these disks Raid 5?
The creation of the files can take very long since they are zeroed as they are written. This can be quite time consuming on some systems.
As for the 32 hours that could be network issues, but given how long to create the files, I think not. I am leaning towards Raid issues.
November 1, 2007 at 8:25 am
OMG. You might want to think about an alternative backup/restore program. We've looked at something called lite speed (www.quest.com).
One of the things I liked about it was the compression level along with being able to set the number of threads, along with object level backup and restore. it's a bit pricey for the entrise edition ($2,000 or so), but worked really well. Plus you get a 10 or 14 day trial to try it.
Unfortunetly, because of politics in the organization, we didn't purchase the product, but that's another story.
DISCLAIMER: I am in no way affliated with Quest software, just think they offer a very good product).
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply