Lock-free Backups

Lock-free backups do not block INSERT, UPDATE, and DELETE operations at any point during a backup. They do not need to lock and wait for write queries. Lock-free backups are enabled by default

However, ALTER DATABASE and SNAPSHOT commands will not run while there is a backup job active on the relevant database. The backup takes the workspace operations lock for the entirety of the backup.

Commands Blocked by the BACKUP Process

  • ALTER DATABASE

  • DROP DATABASE

  • SNAPSHOT DATABASE

Commands Not Blocked by the BACKUP Process

  • CREATE TABLE (columnstore and rowstore)

  • ALTER TABLE (columnstore and rowstore)

  • DROP TABLE (columnstore and rowstore)

  • DELETE TABLE

  • INSERT TABLE

  • TRUNCATE TABLE (columnstore and rowstore)

  • CREATE INDEX

  • DROP INDEX (columnstore and rowstore)

  • CREATE VIEW

  • DROP VIEW

  • ANALYZE TABLE (columnstore and rowstore)

  • OPTIMIZE COLUMNSTORE TABLE

  • SHOW TABLE STATUS

  • SHOW DATABASE STATUS

  • OPTIMIZE ROWSTORE TABLE

Last modified: February 29, 2024

Was this article helpful?