sql server programming: Hi All,

I have a database in a Single user mode and I can't change it backup to Multi
User Mode.

It set the database into a single user mode and then does a backup and copy
it to another database. The backup was succesful but when it did set the
database into a single user mode to a restore to another database it failed.

I did use Alter database to change to Multi_user but it says the database is
in use or a program is using it. I have tried to kill all processes on the
server related to the database. I have stopped Sql Agent, Sql Service and
even rebooted the server but I still have the Database in Single User mode.

Any help will be appreciated.

Thanks.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200610/1

Why do you set the database to single user mode to do a backup?

I suspect you are running the alter database command while connected to
the database - connect to another database (usually master) and it
should be ok.

It's possible that you are unlucky and a checkpoint is being run bi=ut
that would be transient.

www.nigelrivett.net

I use the Master database and still wasn't able to change it to multi User
mode. Below is the command:

Use Master
GO
ALTER DATABASE Database Name
SET MULTI_USER;
GO

I also used this command:

sp_dboption 'Database Name', 'single user', 'False'

I don't know how I can get the database out of this Single User mode.

Any help will be appreciated,

Thanks,
Naana.


[quoted text, click to view]

--
Message posted via http://www.sqlmonster.com

Naana via SQLMonster.com (u14055@uwe) writes:
[quoted text, click to view]

Rather than using KILL for step 2, use

ALTER DATABASE db SET SINGLE USER WITH ROLLBACK IMMEDIATE

this will throw out all users from the database.

RESTORE can still fail, though. To wit, if the RESTORE command is run
from the database you want to restore...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

I was able to change the database from a single user mode to multi user mode
via command prompt. Starting the sql server in sqlservr -m

I have a backup that have 5 steps.

1) backup database
2) kill all connections
3) set database in single user mode
4) restore database
5) open database

The job failed on step 4 - restore database. Error exclusive access could
not be obtained because database is in use, and application error: faulting
application kill.exe,....

Any help.





[quoted text, click to view]

--
Message posted via http://www.sqlmonster.com