1

I have a production SQL Server database (2008R2) that performs full backups every night. The total time for the backup takes about 20 minutes. During the time of the full backup, if anything attempts to connect or interact with the database, it appears as though the DB is offline, or their connection times-out.

I've read where when full backups are performed, that any active CRUD actions are still captured, because the backup includes the DB file and the Log file.

What I'm wondering is if it's possible to have the DB still active (even for Reads) when a Full Backup is being performed? Or do I need to just accept that every night, my DB will be unavailable for some period of time?

FYI - I'm using a standard backup command to perform the backup: BACKUP DATABASE [xxxx] TO DISK = 'xxBackupFilePathxx' WITH COMPRESSION

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
rchadwick
  • 11
  • 1
  • You can absolutely have active operations (including writes) to a database while any native backup operation is ongoing. Is it possible that you're running into some other resource contention (IO would be my first guess, but anything's possible, but CPU would be another likely culprit if you're seeing connection timeouts)? – Ben Thul Feb 20 '19 at 17:02
  • Provide more detail. How is this done? Via a maintenance plan? Some custom code that is part of a scheduled job? – SMor Feb 20 '19 at 18:09
  • Initially, I used a Maintenance Plan to schedule these nightly backups. Then I had issues with the maintenance plan not executing or fully completing - so now I use a scheduled job that executes the command to "BACKUP DATABASE [xxxx] TO DISK". Either way - (maintenance plan, or manual job) - I would expect the end result is that a "BACKUP DATABASE" command is run. – rchadwick Feb 21 '19 at 18:07

0 Answers0