SQL Server Backup – What Happens When Two Processes Perform Full Backup

backupsql serversql-server-2012

I have an instance of SQL Server 2012 RTM running. I have two separate applications that want to do a backup of a database lets call them "AppFull" and "AppFullCopyOnly".

AppFull does a full database backup followed by a truncate of the transaction log. AppFullCopyOnly also does a full database backup but with the COPY_ONLY flag so as not to interfere with the AppFull process.

So my question is: Will these applications interfere with each other if they are both processing the same database? I realise there will be an impact on the server, but what I need to know is whether either backup will not complete, integrity be compromised etc. I need to understand both scenarios namely they run in these orders:

  1. AppFull and then AppFullCopyOnly
  2. AppFullCopyOnly and then AppFull

PS. I cannot control when the applications run so that there is no collision and hence also not their order of execution. I also have no control over them both running – they both have to both run.

Best Answer

Will these applications interfere with each other if they are both processing the same database?

Yes they would affect each other, in terms of blocking, when running "concurrently", the one would block the other. But if they are not running concurrently like you schedule copy_only backup after full backup or before full backup, copy_only full backup is not going to affect full backup and you recovery planning. Basically copy_only full backup just backups up the data without changing the differential base and is not going to affect the restore procedure you have planned.

As a fact a lot people use copy_only backups to get replica of database when its required urgently without affecting the total backup and restore sequence.

Few points:

  • A full backup would block other concurrent full or copy only full backup.

  • A full backup would not block transaction log backup if you are using SQL server 2008 and above. But log will only truncate when full backup finishes. Paul has series about backup I suggest you read it.

  • A differential backup would block concurrent differential backup.

  • A log backup would block concurrent log backup.

  • If you are using SQL Server 2008 and above you can run transaction log backup when any of full or differential backup is running.

This blogs.msdn article has details

but what I need to know is whether either backup will not complete, integrity be compromised etc. I need to understand both scenarios namely they run in these orders:

If you are running concurrently the one started first will block other and the other will finish after the fist one allows it to start. But at the end both will finish

If you are not running concurrently, both will just finish fine. Unless there is some other issue.

In both cases the integrity of backup is NOT compromised.

Related Question