SQL Server Q&A

Symptoms
A restore of the msdb system database leaves the database in a loading state (unrecovered) and changes in the differential and log backups are not applied if the following conditions are true:The restore is performed from Enterprise Manager (SEM).The restore operation is a combination of:
A full database backup and a differential backup.A full database backup and transaction log backups.A full database backup and a combination of differential and transaction log backups.NOTE: To perform transaction log backups of the msdb database in SQL Server 2000, you must change the recovery model to “bulk logged” or “full.” In SQL Server 7.0, you must have the “trunc. log on chkpt.” setting turned off for the msdb database.
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
301511?(http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

Symptoms
Consider the following scenario. The SQL Writer service is running on a computer that has Microsoft SQL Server 2005 installed. You use SQL Server Management Studio to back up a database. Additionally, you use the Backup tool (NTBackup.exe) to back up the database files. Then, you try to restore the database from the backup set by using SQL Server Management Studio. In this scenario, you receive an error message that resembles the following:

Restore failed for Server ‘<ServerName>’.(Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device ‘<GUID of the device>’. Operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)If you examine the start date and the finish date of the backup set, you find that the dates are updated to the date that you used the Backup tool.
Resolution
This problem occurs because the Backup tool uses the SQL Writer service to back up SQL Server 2005 databases. The Backup tool triggers the SQL Writer service to create a snapshot. After the SQL Writer service creates the snapshot, the Backup tool determines whether the database files are on the exclude list. The database files include .ldf files and .mdf files. If the database files are on the exclude list, the Backup tool deletes the database files from the snapshot. However, the Backup tool adds entries in the backupset table in the msdb database.

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”

Symptoms
SQL Server Enterprise Manager may allow you to select and restore backups that no longer exist. Enterprise Manager may then either restore the latest backup or an error may occur while restoring.
Resolution
Enterprise Manager reads the backupset and backupmediafamily tables from the msdb database to populate the restore database screen with the list of backups. These tables store a history of backups. The tables are not updated when a backup is overwritten with a BACKUP WITH INIT statement.

Symptoms
In Microsoft SQL Server 2008, you may experience the following issues.
Issue 1In SQL Server Management Studio, you connect to an instance of SQL Server 2008 as a user who is not a member of the sysadmin fixed server role. When you try to expand the Databases node in Object Explorer, you receive the following error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server principal “<User>” is not able to access the database “<Database>” under the current security context. (Microsoft SQL Server, Error: 916)
Issue2You have lots of database backups. In SQL Server Management Studio, it takes a long time to expand the Databases node in Object Explorer. Additionally, when you try to run a query that returns the following information from the backupset system table in the msdb database, the query takes a long time to run: The date of the last log backupThe date of the last backupThe date of the last differential backup
Issue 3When you run the sp_spaceused stored procedure to return the size of the current database and the space that is used by indexes, the sp_spaceused stored procedure takes a long time to run.
Resolution
Cause 1When a user who is not an administrator tries to access some database properties, the user does not have permission to access some catalog views. Therefore, an exception occurs in the SQL Server Management Objects (SMO) enumerator.
Cause 2If you have lots of database backups, lots of rows exist in the backupset systemtable in the msdb database. SQL Server Management Studio runs a query against the backupset table when you to try to expand the Databases node in SQL Server Management Studio.

Symptoms
Consider the following scenario. You create a maintenance plan in an instance of Microsoft SQL Server 2008. You perform a full backup for the MSDB database.You restore the MSDB database to another instance of SQL Server 2008. You delete the maintenance plan in the second instance by using SQL Server Management Studio.In this scenario, the original maintenance plan in the first instance is deleted unexpectedly. When you refresh the maintenance plans folder in SQL Server Management Studio in the second instance, the maintenance plan is not deleted from the second instance.
Resolution
A maintenance plan is aSQL Server Integration Services (SSIS) package that is stored as XML in the MSDB database. A maintenance plan contains a list of connection strings that use the actual name of the server. Therefore, if you restore the MSDB database of a server to another server, the maintenance plan connection strings still point to the original server.