December 5, 2004 at 2:11 am
Hi All,
I have database backup (FULL) sizing about 99GB on my Harddisk. I am restoring it from last night and it is over 8 hours (I don't see any change in restore progress bar). In Task Manager, application is live. On Hard disk 2 files (database/ log files created) and when I try to attach it said files being used by other processor. This seems that restoration still in progress. My questions:
1) Is there quick way to restore? How much it takes for 100GB size?
2) Is this possible to restore without transcation log (making new while restoring)?
Please give me script to achieve above?
I shall appreciate if you some refer me so SQL Recovery tool who can read SQL Backups and restore on object level.
Thanks in advance for your help.
NM
December 5, 2004 at 3:49 pm
How long it takes would depend on a number of factors Im guessing. Such as where you are restoring from (disk or tape), cpu's , RAM etc. I have a 55 GB database that takes me under two hours to restore. I don't think its possible to restore at object level.
I would be interested to find if there is a formula for calculating restore times which takes into account hardware specs though...anyone?
Regards,
Terry
December 5, 2004 at 4:00 pm
Hi Terry,
It was stored on same Server but different RAID5 Disk. I measure Disk I/O at 100- 130MB/ sec. It took me over 16 hours to restore it since my log file was alone 152GB (which I did not noticed).
It took such an unexpected time due to FULL RECOVERY model set for this database. I have now set to SIMPLE. I have also done couple of things to restrict log file growth, now doing disfragmentation using Diskeep 8.0 Server edition.
You asked good question about calculating restore time based on database model. Is there one in forum to shed some light on it?
Appreciate if someone refer some good and reliable tool to zip SQL database to converse space and save time. I am evaluating now SQL LiteSpeed.
Thanks.
December 5, 2004 at 7:21 pm
You might start here:
Four of a Kind - Backup Software Shootout
Four of a Kind - Backup Software Shootout Part 2
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
December 7, 2004 at 10:25 pm
To approximate backup/restore times, you really would have to run benchmarks on your own configuration. Even then, there are a lot of other facters that need to be taken into account (like what else the server might be doing while restoring).
Unless the database had autogrow set with a small incremental growth percent or size, file defragmentation may not be as much of a problem as you think. Defragmenting the drive can never hurt, but it probably won't give you the perfomance boost you are looking for.
Object level restore was removed from SQL Server. I believe the last version that had it was 6.5. In 7, the entire storage architecture was changed.
One other note, I would make sure that point in time recovery is not needed if you use 'simple'. If this is a production system, you might be better served managing log size by using bulk-logged or just backing the log up frequently to remove committed transactions.
- Jay
December 8, 2004 at 4:15 am
Thanks for the tips all...particularly the posting on the backup/restore tools....very handy.
Regards,
Terry
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply