please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.
What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?
For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?
Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...
bak is the database backup
trn is the transaction log file backup
It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.
Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.
The backup will slow down all processes on the server but will not stop any activity on the database.