June 16, 2008 at 1:14 pm
When I backup 100GB Database it takes 1 hour to finish the backup, but when I restore it takes thrice the time it took to backup(to restore it took 3 hours)
I tested 3 times.
I also retore this file on another server over the network that also took 3 hours what should I do?
June 16, 2008 at 1:19 pm
When you restore, does the database exist?
What if you had a database and restored over it. Still 3 hours? are the backups local on the server?
June 16, 2008 at 1:34 pm
I had the DB and I am overwriting it.
I did both way from Local it is faster but from network it is slower.
June 16, 2008 at 2:08 pm
it probably will be slower accross the network.
If you restore is local, does the backup exist on the same drive as the database files. this can make restores perform poorly too
Gethyn Elliswww.gethynellis.com
June 16, 2008 at 2:37 pm
You can speed up network backups and restores by using multiple backup files. Backup/restore will (almost (TM)) always be slower over a network vs. a local drive.
BACKUP DATABASE Foo
TO DISK = '\\Server\Share\Foo_01.BAK'
, DISK = '\\Server\Share\Foo_02.BAK'
, DISK = '\\Server\Share\Foo_02.BAK'
WITH INIT
RESTORE DATABASE Foo
FROM DISK = '\\Server\Share\Foo_01.BAK'
, DISK = '\\Server\Share\Foo_02.BAK'
, DISK = '\\Server\Share\Foo_02.BAK'
WITH REPLACE, RECOVERY
If you're network will achieve 50MB/s per file, you've just (theoretically (TM)) just achieved a 150MB/s io operation. Proper usage of multiple backup files can achieve quite decent throughput. Best practice (IMO) is to apply N-1 threads, where N = number of cpus.
I've got a really good network and SAN attached systems at my current location. I can basically saturate an HBA at ~200MB/s sustained if I wanted to push the i/o that hard.
Your friendly High-Tech Janitor... 🙂
June 17, 2008 at 8:32 am
And one thing I know is true (for SQL2K, anyway), SQL will size your files (.mdf, .ndf, .ldf) to be as big as the source database for the restoration process. I've seen that portion of the restore process take a substational amount of time to complete. Perhaps create an empty database properly sized prior to restoring???
-- You can't be late until you show up.
June 18, 2008 at 7:35 am
I Agree with Gordon. with init it shows you the %age how much got restored on the DB. Make sure with your log file data file should not be on the same drive, if this is the case thats'y it is taking extra time to restore. And normally for backup it will not take more time but while restoring it takes more than the Backup.
MCP, MCTS (GDBA/EDA)
June 18, 2008 at 7:41 am
Manoj (6/18/2008)
with init it shows you the %age how much got restored on the DB.
INIT just wipes any existing file, it doesn't have anything to do with the stats output. Percentage complete (Stats) is always returned as part of a backup/restore operation. You can control the values at which stats are returned (default is every 5%) by setting a STATS value.
BACKUP DATABASE Foo
TO DISK = 'Foo.BAK'
WITH INIT,
STATS = 15
This will output stats every 15%.
Your friendly High-Tech Janitor... 🙂
June 18, 2008 at 2:33 pm
A restore would be expected to take longer than the initial backup because you have to write to the disks, not just read from them. Your RAID type can also affect the restore time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply