Microsoft KB Archive/257857

= BUG: Transaction Log Backup Possible After the Addition or Removal of Database Files =

Article ID: 257857

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q257857



BUG #: 57703 (SQLBUG_70)



SYMPTOMS
According to the "Creating and Applying Transaction Log Backups" topic in SQL Server 7.0 Books Online, after a database file is added or removed from the database, you should perform a full database backup instead of a transaction log backup.

Because the information about the change in the database file structure is not included in the transaction log, this process breaks an existing sequence of transaction log backups. You need to perform a new full database backup to start a new sequence of transaction log backups.

However, even though this is recommended in SQL Server Books Online, SQL Server does not enforce this behavior but permits you to perform an apparently valid transaction log backup at that time without giving a warning that the backup sequence is broken.

If such an invalid backup is made and then an attempt to restore it is made later, the attempt to restore this transaction log 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, perform a full database backup after modifying the file structure of a database.



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



Steps to Reproduce the Problem
Use the following steps to reproduce the problem:

  Use the following statements to create a database and begin a backup series: create database test go backup database test to disk='c:\test_dump.bak' with INIT go   Use the following statement to add an additional file to the database: ALTER DATABASE test ADD FILE (NAME = Testalt,  FILENAME = 'c:\testalt.ndf',   SIZE = 1MB,   MAXSIZE = 5MB,   FILEGROWTH = 1MB) GO   Use the following statements to continue the backup chain: backup log test to disk='c:\test_dump.bak' with noinit go   Use the following statement 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 

At this point, you receive the following error message:

Server: Msg 3155, Level 16, State 1, Line 6

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 6

Backup or restore operation terminating abnormally.

Keywords: kbbug kbpending KB257857

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.