Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 911847 - Last Review: December 29, 2005 - Revision: 1.1
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
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'
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
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 Edition
| kbsql2005engine kbexpertiseadvanced kbprb KB911847 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please