BUG: Transaction Log Backup Possible After Automatic Rebuild of LDF

Article translations Article translations
Article ID: 257855 - View products that this article applies to.
This article was previously published under Q257855
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 57670 (SQLBUG_70)
Expand all | Collapse all

On This Page

Symptoms

If the log data file (LDF) for a database is not available during SQL Server startup (for example, if the file has been renamed or deleted), SQL Server 7.0 sometimes attempts to rebuild the LDF file automatically to ensure database availability.

Because the information from the original LDF file is lost, this process interrupts an existing sequence of transaction log backups. A new full database backup needs to be performed to start a new sequence on transaction log backups.

However, SQL Server permits you to perform an apparently valid transaction log backup at this time without warning you that the backup sequence is broken.

If you do make such an invalid backup and then later attempt to restore this transaction log, it fails with the following message:
Server: Msg 3155, Level 16, State 1, Line 1
The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created.

Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

Workaround

To work around this problem, do either of the following:
  • Make sure that the LDF files are not deleted or renamed.

    -or-
  • Perform a full database backup to start the transaction log backup sequence over.

Status

Microsoft has confirmed this to be a problem in SQL Server 7.0.

More information

Steps to Reproduce the Problem

  1. Use the following statements to create a database, insert data, and begin a backup series:
    create database test
    go
    
    use test
    go
    create table t (c int)
    go
    insert into t values (1)
    go
    
    backup database test to disk='c:\test_dump.bak' WITH INIT
    go
    insert into t values (2)
    go
    backup log test to disk='c:\test_dump.bak' WITH NOINIT
    go
    
    insert into t values (3)
    go
    
    use master
  2. Use the following steps to let SQL Server create a new LDF file for the database on startup:
    1. Make that no user is connected to the database.
    2. Stop the SQL Server service.
    3. Rename the test_log.ldf file.
    4. Start the SQL Server service.
  3. Check for the following kind of entries in the SQL Server error log to confirm that a new LDF file has been created:
    File c:\MSSQL7\data\test_log.LDF does not exist - unable to activate.
    Attempting to rebuild primary log file for database test.
    ...
    New log file f:\MSSQL7\data\test_log.LDF built.
  4. Use the following statements to continue the backup chain:
    use test
    go
    insert into t values (4)
    go
    backup log test to disk='c:\test_dump.bak' WITH NOINIT
    go


  5. Use the following statements to attempt to restore the backups into a new database named testrestore:
    RESTORE DATABASE testrestore
       FROM disk='c:\test_dump.bak'
       WITH NORECOVERY, 
       MOVE 'test' TO 'c:\testrestore.mdf', 
       MOVE 'test_log' TO 'c:\testrestore.ldf'
    RESTORE LOG testrestore
       FROM disk='c:\test_dump.bak'
       WITH NORECOVERY,FILE = 2
    RESTORE LOG testrestore
       FROM disk='c:\test_dump.bak'
       WITH FILE = 3, RECOVERY
At this point, you receive the following error message:
Server: Msg 3155, Level 16, State 1, Line 1
The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created.

Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

Properties

Article ID: 257855 - Last Review: November 2, 2013 - Revision: 2.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug KB257855

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com