The user access option of a SQL Server 2005 database may unexpectedly be set to SINGLE_USER when you restore the database by using a backup file (911847)

The information in this article applies to:

Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Workgroup

Bug #: 402384 (SQL BUDT)
Bug #: 44488 (Content Maintenance)

SYMPTOMS

Consider the following scenario. In a Microsoft SQL Server 2005 database, you change the user access option from MULTI_USER to SINGLE_USER, and then set the database to read-only. Then, you change the user access option back to MULTI_USER. When you back up the database and then restore it by using the backup file, the user access option of the database may still be set to SINGLE_USER.

CAUSE

This problem may occur if the user access option of the database is incorrectly set to SINGLE_USER when you restore the database.

WORKAROUND

To work around this problem, manually set the user access option of the database back to MULTI_USER after you use the backup file to restore the database.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

To determine whether the user access option of the database is set to SINGLE_USER, run the following Transact-SQL statement against the database by using SQL Server Management Studio.

Select is_read_only as ReadOnly,
user_access_desc as UserAccess
From sys.databases Where name='DatabaseName'

NoteDatabaseName is a placeholder for the name of the database.

When you run the Transact-SQL statement, you may receive a result that is similar to the following:

ReadOnly  UserAccess
--------  -----------
1         SINGLE_USER

If the value in the ReadOnly column is 1, the database is set to read-only.

REFERENCES

For more information about how to back up and restore a database, see the following topics in SQL Server 2005 Books Online:

How to: Back Up a Database (SQL Server Management Studio)
How to: Restore a Backup from a Device (SQL Server Management Studio)

For more information about how to change database properties, see the following topics in SQL Server 2005 Books Online:

DATABASEPROPERTY (Transact-SQL)
How to: Change the Configuration Settings for a Database (SQL Server Management Studio)
Database Properties (Options Page)
Setting Database Options

Modification Type:	Major	Last Reviewed:	12/29/2005
Keywords:	kbsql2005engine kbExpertiseAdvanced kbprb KB911847 kbAudDeveloper kbAudITPRO