Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a data base with a size up to 120 Gb and I need do a backup every night.

The problem is that the system needs stand operative all the 24 hours.

In this moments the backup takes up to 2 hours to complete.

How I can do the backup without compromising server performance?

Thanks, Andrés

share|improve this question

migrated from stackoverflow.com Jul 12 '13 at 11:43

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

2 Answers 2

120GB should not take 2 hours

  • Do you have a separate disk array for backups, with a separate network interface to move the backups off the server?
  • If you have Enterprise Edition, can you use mirror backups
  • Can you do weekly full backups and daily differentials?
  • Is backup compression turned on (Standard in SQL Server 2008 R2+, Enterprise only in SQL Server 2008)

And without more details, it will be hard to be more specific

share|improve this answer
    
I made the backup over other server, to minimize the I/O of the disk. –  Andres Jul 12 '13 at 10:55
    
@Andres: then you are limited by the network speed and traffic then –  gbn Jul 12 '13 at 11:22
    
If I make a copy in mirror, this does not compromise the performance to be continuously synchronizing the changes? –  Andres Jul 12 '13 at 11:36
    
About compression, the compression of the copy is active –  Andres Jul 12 '13 at 11:38
    
Seriosiuly I take backeups of 400gb databases at the moment. Twice per day. It does not take 2 hours and perfomrmance during the backup is totally ok - a LITTLE lower, but not impacted. –  TomTom Jul 12 '13 at 17:31

Its very unlikely that your database changes by 120GB every day. Im not sure about the specifics with sql-server but one backup should taken every month or so, and then incremental backups in between. You may even be able to merge the backed up data with the incremental data as part of a script so the restore doesnt take the time running from 1 + (days since last whole backup) to update.

You could also replicate and take a backup of the replicated server which wouldnt effect the main server performance

share|improve this answer

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.