SQL Server Performance

3 db mirror questions on backups:

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Jan 15, 2012.

  1. Trev256b Member

    1) can database on principal be backed up with FULL database backup without affecting LSN transaction log sequence?
    2) can TRANSACTION LOG backup of database on principal be backed up without affecting LSN transaction log sequence?
    3) If transaction log can't be backed up on principal - how is the transaction log maintained?
  2. davidfarr Member

    (1) No
    (2) No
    (3) What do you mean by "maintained" ?
    Backup files do not consist of object data only. Backups of the transaction log also contain the order in which the latest data was added/updated/deleted, with a time record, which is of critical importance during recovery.

    In simple terms, the LSN can be thought of as a kind of sequential transaction number marker. A full database backup will include all data from the data file and log file. Let us say the last LSN in a full backup file is 12345.
    A transaction log backup after this full backup would then start at 12346 and might end at 12350.
    The next transaction log backup would then start at 12351 and might end at 12377.
    A backup of the transaction log will only include transactions that have not yet been backed up already. When a transaction log is backed up, it will 'truncate' itself (move all relevant data to the data file to free up space for more transactions). SQL server keeps track of all this with the LSN value in the files.

    It can be seen therefore that every backup you make will contain a new LSN number.
    Backup files can only be restored in the correct LSN sequence, starting with the last full backup file and then restoring all transaction log files in the correct sequential order You cannot skip a file because then that data would be missing, no other backup file will have it.

    An older full backup file and transaction log backups taken between the time of the old full backup and next full backup, can still be used to restore an old copy of a database, but it would then be as old as the last transaction log that was restored to it. An old database copy such as this can be useful in a test environment where current data is not critical.
  3. Trev256b Member

    sorry - my questions were unclear - let me try again:
    1) If you run a full database backup on the procipal database, will database mirroring still work?
    2) Does database mirroring need an uninterrupted LSN log sequence to carry on working?
    3) If you run a transaction log backup on the principal database, will database mirroring still work?
    4) If you can't backup a transaction log on a proincpal how can you maintain/control the size of the transaction log?

    Please let me know if this is not clear? Thanks.
  4. davidfarr Member

    My bad. I definitely misunderstood the original post :) I had thought you were referring to the LSN numbers contained in the backup files themselves.
    New answers:
    (1) Yes. You can run any backup (full, differential, log) on the principal database, except "BACKUP LOG WITH NORECOVERY"
    (2) Yes, but the LSN numbers in the log file still maintain sequence even when performing backups. SQL server will not truncate log transactions that have not yet been written to the mirror. It is similar in some ways to transactional replication.
    (3) Yes. See answer (1) above.
    (4) You can backup the log, which will then truncate to free up space.

Share This Page