Restore from Backup taking a long time

[Ranvier]

Hi,

I'm creating a Test database from a backup of Live. The database is about 1 GB and the Log file is about 22 GB. It is taking ages to restore about 40 min so far - is this normal for a datbase this size or am I just being impatient? (BTW: There is loads of disk space available).

Any comments would be helful

Thanks

 

[Ranvier]

Well, in answer to my own question - I was being impatient. The restore completed successfully - it obviously just takes a long time.

 

[ptheriault]

Why is your log so large! Are you backing it up? The reason your restore took so long was because of the size of your log. I would truncate it.

Run this command.

BACKUP LOG <your DB> WITH TRUNCATEONLY



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ranvier]

Thanks for the advice. I dont usually look after the server i'm doing this job on and don't know a geat deal about how the log would/could get so large. How would one avoid a log file getting so lare in the first place? Any known useful links or info on the subject would be appreciated.

Thanks

 

[ptheriault]

You have two options to control the size of you log.
1. Back it up.
2. Set the database to simple recovery.
Warning* If you select #2 you can not restore your database to a point in time.

Also look up DBCC SHRINKFILE in BOL to gain back some of the used space.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[oracleSQLdba]

The restore took so long because the log file had to be created. In sql 2000 this is a sloo

http://wwwwww

process. SQL 2005 is supposed to have instant file initialization though I can't vouch for it.

 

[Ranvier]

Ok, The database is set to Full Recovery - this is not going to be changed. The databse gets backed up every night using SQL Server Agent (not entirely sure what that is). Would I not need to Shrink the log file (using Shrink Database in Enterprise Manager?) in order to reduce the size of the LDF file. Also before I shrink the log file what do I have to do before that (Backup the DB?, Backup Log Transaction?).

Another question if you can anwer it - when doing a full backup, does this include backing up the transaction logs or is this a seperate process?

Thanks in advance.

 

[ptheriault]

You have to backup the transaction log. If you don't it will never clear commited transactions from the log. Create scheduled job to backup the log. If you need to recover to a point in time then you will also need to have the job run every N minutes throughout the day. N being the logest amount of time for data loss you could afford. for example I run incremental log backups every 30 minutes throughout the day. Here is an example of my backup schedule.

12 AM FUll DB backup. (runs 30 Min)
1 AM Init my log device
1:30 AM - 11:30 PM every 30 min log backup.

- Paul
- If at first you don't succeed, find out if the loser gets anything.