nerdexam
Microsoft

70-466 · Question #118

You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions…

The correct answer is B. BACKUP LOG DB1 TO Device1. A standard log backup (BACKUP LOG) is the correct way to free space from a full transaction log without breaking the LSN chain required for point-in-time restore continuity.

Configure and maintain an analysis services database

Question

You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs). Which code segment should you execute?

Options

  • ABACKUP LOG DB1 TO Device1 WITH COPY_ONLY
  • BBACKUP LOG DB1 TO Device1
  • CBACKUP LOG DB1 TO Device1 WITH NCRECOVERY
  • DBACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY

How the community answered

(59 responses)
  • A
    3% (2)
  • B
    93% (55)
  • C
    2% (1)
  • D
    2% (1)

Why each option

A standard log backup (BACKUP LOG) is the correct way to free space from a full transaction log without breaking the LSN chain required for point-in-time restore continuity.

ABACKUP LOG DB1 TO Device1 WITH COPY_ONLY

COPY_ONLY log backup backs up the log without marking VLFs as truncatable, so it does not free space in the log file and will not resolve the full log condition.

BBACKUP LOG DB1 TO Device1Correct

BACKUP LOG DB1 TO Device1 performs a standard transaction log backup, which backs up all log records since the last log backup and then marks the backed-up virtual log files (VLFs) as reusable, freeing space in the log file. Critically, it maintains the unbroken chain of log sequence numbers, preserving the ability to perform point-in-time restores. This is the correct and supported method in SQL Server 2012.

CBACKUP LOG DB1 TO Device1 WITH NCRECOVERY

NORECOVERY (not NCRECOVERY) is a restore option, not a valid BACKUP LOG option - this syntax is not valid and would not execute.

DBACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY

TRUNCATE_ONLY was deprecated in SQL Server 2008 and removed in SQL Server 2012, so this option is not available; additionally it discarded log records without backing them up, breaking the LSN chain.

Concept tested: SQL Server transaction log backup and LSN chain management

Source: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server

Topics

#transaction log backup#LSN chain#log full recovery#BACKUP LOG

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice