locked
How to perform a full backup for export/import and guarantee no FK errors (transaction consistent DB) RRS feed

  • Question

  • In the maintenance plan for our environment there are two tasks in the full backup. One task creates a .bak file and the other a file containing the backup of the log files.

    Should I restore both files when I import the database to ensure a transaction consistent DB so I don't get FK errors on import?

    Or should I only restore the .bak file?

    I don’t understand where the full backup maintenance plan guarantees consistency to avoid FK errors upon import.

    Friday, August 19, 2016 12:36 PM

Answers

  • Hello,

    Restoring a full backup will always create an exact copy of the source database; if you don't have any issues in the source database the you will never have any issues in the restored database; so there is no need worrying about FK errors.

    The log backup contains transaction changes since the last full or log backup; it has no effect of what you worry  about.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Kalman Toth Friday, August 19, 2016 8:36 PM
    • Marked as answer by scminter007 Thursday, August 25, 2016 2:41 PM
    Friday, August 19, 2016 8:22 PM

All replies

  • Hello,

    Restoring a full backup will always create an exact copy of the source database; if you don't have any issues in the source database the you will never have any issues in the restored database; so there is no need worrying about FK errors.

    The log backup contains transaction changes since the last full or log backup; it has no effect of what you worry  about.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Kalman Toth Friday, August 19, 2016 8:36 PM
    • Marked as answer by scminter007 Thursday, August 25, 2016 2:41 PM
    Friday, August 19, 2016 8:22 PM
  • My learning continues. The full backup maintenance job on a database in full recovery mode (not simple) backs up the logfile during the full backup which will guarantee consistency.

    I found this https://msdn.microsoft.com/en-us/library/ms186289.aspx

    Thursday, August 25, 2016 2:43 PM
  • My learning continues. I found this article as well: https://msdn.microsoft.com/en-us/library/ms186289.aspx

    which shows the log is backed up during the full backup when the database is in full recovery model.

    Thursday, August 25, 2016 2:45 PM