SQL Server Q&A

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by this hotfix packageThe prerequisites for installing the hotfix packageWhether you must restart the computer after you install the hotfix packageWhether the hotfix package is replaced by any other hotfix packageWhether you must make any registry changesThe files that are contained in the hotfix package
Resolution
When you restore a transaction-log backup that is generated from an instance of Microsoft SQL Server 2000 with Service Pack 4 (SP4) to an instance of Microsoft SQL Server 2005, you receive anerror message intermittently. The error message resembles the following:

Msg 3456, Level 16, State 1, Line 1
Could not redo log record (11841:73811:36), for transaction ID (0:13210866), on page (3:7), database ‘<DatabaseName>’ (database ID 6). Page: LSN = (11827:5010:2), type = 17. Log: OpCode = 17, context 25, PrevPageLSN: (11841:73692:1017).
Restore from a backup of the database, or repair the database.

Symptoms
When you perform a full backup of a database in Microsoft SQL Server 2005, you may receive an error message that resembles the following:

Backup failed for Server ‘ComputerName\SQLInstanceName’.(Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The backup of the file or filegroup “sysft_ FullTextCatalog” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)If you perform a full backup of a database in SQL Server 2005 Service Pack 2 (SP2), you may receive an error message that resembles the following:

The backup of full-text catalog ‘FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog becomes offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Resolution
This behavior occurs because a full-text catalog in the database is not online. To perform a full backup, SQL Server 2005 requires all the database files andfull-text catalogs in the database to be online.
The full-text catalog may be online because one or more of the following conditions are true:The full-text catalog folder is either deleted or corrupted.You did not enable the database for full-text indexing.The database is restored from a Microsoft SQL Server 2000 database backup. Therefore, the folder of the full-text catalog in the database does not exist on the server where you restore the database.The instance of SQL Server 2005 that you are running was upgraded from SQL Server 2000. However, the full-text search service cannot be accessed during the upgrade.The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.Note In SQL Server 2005 SP2, the reason that the full-text catalog is not online is logged in the SQL Server error log. See the SQL Server error log for the specific cause of this behavior.

Symptoms
When you perform a database backup to disk or tape, or a restore from disk or tape, the following error message may occur:
SQL Server 7.0

Server: Msg 3266, Level 16, State 1, Line 1
The Microsoft Tape Format (MTF) soft filemark database on backup device ‘devicename’ cannot be read, inhibiting random access.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally. SQL Server 2000

Server: Msg 3266, Level 16, State 1, Line 1
The backup data in ‘devicename’ is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally. SQL Server 2005

Server: Msg 3013, Level 16, State 1, Line 1
The backup data at the end of ‘devicename’ is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Resolution
A filemark in the backup device could not be read. There are many reasons why you may encounter a filemark error. Some of the reasons include the following :A media failure may occur on the device where the backup is located.A write failure may occur during the creation of the backup.
For example, a loss of connectivity may occur during a network backup. Or, a failure of the IO path to flush the write to disk may occur after the write to disk was reported to SQL server as successful.

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
The sqlmaint utility does not generate a message when attempting to back up the transaction log from a database that has the truncate log on checkpoint option set. This is also true when using the Database Maintenance Plan Wizard.
The backup of the log takes place and a blank file is created. However, if you run the backup by using the SQL Query Analyzer, the following error message occurs:

Server: Msg 4208, Level 16, State 1, Line 1
BACKUP LOG is not allowed while the trunc. log on chkpt. option is enabled. Use BACKUP DATABASE or disable the option using sp_dboption.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
You can get SQL Server to report an error message if you have it write to either an HTML file or to a table in the msdb database. Use the following Sqlmaint.exe parameters:HtmlRpt, which writes to an HTML file.
-or-
WriteHistory, which writes a history to msdb.dbo.sysdbmaintplan_history.For example:

C:\MSSQL7\Binn>sqlmaint -D pubs -S servername -U sa -BkUpLog “c:\temp\pubslog.bak” -BkUpMedia DISK -UseDefDir -HtmlRpt “c:\temp\sqlmaint.htm”