I am expecting to have a long backup on a certain database and I was wondering if the application can access (read only? read/write?) it while it is being backed up.
If read/write access would be available then how will consistency be achieved? Probably only a snapshot of what was available at the time of the start of the backup will be included in the backup?
Or will the database be non-operational during backup?
Yes the database is available for use during the backup.
The way the backup process works is basically this.
When the backup database command is taken, the server triggers a checkpoint. Then the database backup is started, and no more checkpoints are issued. When the data is backed up from the database files the transaction log is then backed up. When the backup of the log is complete another checkpoint is issued.
The backup is consistent at all times, and is current as of the end of the backup job.
