Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I often need to to backup and restore database(SQL Server 2000/2005). Now if database is of size > 1 GB restoration process is real time consuming.

Is there any way using which i can truncate the logs or reduce the database size? Thanks all,

share|improve this question
5  
For the record, 1gb is a pretty small database. –  keithwarren7 Jan 26 '10 at 13:23
    
define 'real time consuming' 30 seconds? 1 minute? 10 minutes? –  Nick Kavadias Jan 27 '10 at 6:35
add comment

migrated from stackoverflow.com Jan 27 '10 at 6:09

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 2 down vote accepted

In order to speed up your backup and restore process you may want to consider using third party backup technology such as.

Quest Litespeed

On a side note, modyfying the size of your Transaction Log will not influence the volume of data that is being backed up/restored.

share|improve this answer
add comment

What exactly do you mean by "time-consuming"?

Restoring a 10 GB database should only take a couple of minutes. So no big deal at all. If you need to do this on a regular basis, create a job for that and schedule it to run before you get into the office.

Any action would have to take place before you backup the database, but I wouldn't recommend shrinking the files if you are going to restore the database on a production system.

share|improve this answer
add comment

1 GB is quite small, really...

share|improve this answer
add comment

As part of a Maintenance plan you could Shrink the Database, or the individual files (Data, Log).

This sort of thing could be run at the weekend to help keep the database as small as possible.

share|improve this answer
    
I would really not recommend you shrink databases as part of routine maintenance, it causes fragmentation sqlskills.com/BLOGS/PAUL/post/… it should be an extreme measure not a routine one –  CPU_BUSY Jan 27 '10 at 6:46
    
In the past I would normally back this up with a reindex of all tables after the shrink job so that it got around the fragmentation. Of course, this can be time consiming based on size of your database, as well as number of indexes on there. –  kevchadders Feb 24 '10 at 8:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.