We have a small (125MB) but important (accessed by 50 users) SQL Server
2000 database that is backed up nightly. The SQL backup process takes
less than a minute. What would users experience if we made a full or
differential backup of the database during the day when client apps were
doing reads/writes? Would the backup recognize a write-in-progress and
let it finish before commencing? Are reads possible during the backup?

Thanks

timo,

They will probably notice nothing at all, but backing up does take some CPU
and I/O so there is a small increase in the load on the server.

Backups are fully transactionally complete no matter what reading and
writing is going on.

Backup does not wait for a write-in-progress on the data, but it backs up
all the data pages as rapidly as possible, then adds the transaction log
entries through the time of the completion of the backup. This allows a
restore of the backup to bring the database back to the state of the
database at the end of the backup, not at the beginning of the backup.

RLF

"timo" <timo (AT) thatsmy (DOT) biz> wrote