Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Red face Unanswered: backups explained

    Beginner's query.
    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.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Databases can be backed up while on-line.
    Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.

  3. #3
    Join Date
    Nov 2003
    Posts
    3

    Question OFA for db's

    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...

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    mdf file is the database file
    ldf is the log file

    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.
    Regards
    Dbabren

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    3
    thanks all
    appreciate your time
    have just been on a sql 2000 admin course so it all sounds alot smipler now!
    cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •