SQL Server Q&A

Symptoms
When you run a RESTORE DATABASE WITH RECOVERY statement to bring a standby server online, you might receive one of the following error messages:

Error: 9003
The LSN %S_LSN passed to log scan in database ‘%.*ls’ is invalid.

Error: 9004
An error occurred while processing the log for database ‘%.*ls’.
Resolution
This problem occurs because physical characteristics (virtual log parity) for inactive virtual log files in the transaction log are not preserved when a full database backup is restored.

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.
Resolution
To work around this problem, perform a full database backup after modifying the file structure of a database.

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.
Resolution
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.

Symptoms
Restoring a database backup that was created while a previous login owned the database may result in a database owner (DBO) mismatch. After the database is restored, the sp_helpdb and sp_helpuser procedures may return different DBOs for that database.
Resolution
The database backup contains DBO information from before the ownership change, while the master database contains the current ownership information. The restore operation does not reset the database owner.

Symptoms
For a database with two or more file groups, if a clustered index is created on a table in each filegroup, an attempt to back up any individual file group may fail with the following 3029 error message:

Server: Msg 3029, Level 16, State 1, Line 2
Database file ‘DB_DataA’ is subject to logical recovery and must be among the files to be backed up as part of the file or filegroup backup.
Server: Msg 3029, Level 16, State 1, Line 2
Database file ‘DB_DataB’ is subject to logical recovery and must be among the files to be backed up as part of the file or filegroup backup.
Server: Msg 3013, Level 16, State 1, Line 2
Backup or restore operation terminating abnormally.
Resolution
Perform a full database backup before you attempt to make file or file group backups. After the full backup, you can back up individual file groups.