SQL Server – Database Backup and Saving Simultaneously

backupsql server

What will happen when is running differential backup on SQL Server 2012 and some application is trying save data to table at the same time?

Best Answer

SQL Server allow for a database backup while the database is still being used. During a backup, most operations are possible;

for example

 INSERT
 UPDATE
 DELETE

statements are allowed during a backup operation.

Note : if you try to start a backup operation while a database file is being created or deleted, the backup operation waits until the create or delete operation is finished or the backup times out.

Importent Note :some Operations that cannot run during a database backup or transaction log backup include the following:

1) File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.

2)Shrink database or shrink file operations. This includes auto-shrink operations.

3)If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

3)If a backup operation overlaps with a file-management operation or shrink operation, a conflict occurs. Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out. (The time-out period is controlled by a session time-out setting.) If the lock is released during the time-out period, the second operation continues. If the lock times out, the second operation fails.

Reference :msdn

You can refer this link it explain the scenario with nice examples

Related Question