need to rename database-- other sessions interfere

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

need to rename database-- other sessions interfere

Author

Topic

MrTexasFreedom
Starting Member

13 Posts

Posted - 2010-12-08 : 19:45:13

Folks,

I'm trying to replace an existing database with a restoration from another source. I can't do a restore directly because I have these client processes that keep connecting to the DB as fast as I kill them. I don't have control over those clients.

I've got my restored database as a different logical name (new_Test562)on the server. I just need to get rid of the old database (Test562)and rename the new one to it's current name (Test562).


exec sp_who

kill 99
kill 103
kill 104
kill 117
go

use master
go

ALTER DATABASE Test562 SET ONLINE
go
ALTER DATABASE Test562 SET SINGLE_USER WITH NO_WAIT
go
ALTER DATABASE Test562 MODIFY NAME = old_Test562
go
ALTER DATABASE new_Test562 MODIFY NAME = Test562
go

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'Test562'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.

Any suggestions for preventing these clients from connecting while I'm trying to rename the existing database would be wonderful!

Appreciatively,

MrTexasFreedom

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-08 : 20:01:07

Use this instead:

ALTER DATABASE Test562 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 03:10:59

Or you can change the database to OFFLINE (using ROLLBACK IMMEDIATE as above). My preference is SINGLE_USER but if you are going to need to reboot, say, it may be preferable to use OFFLINE so nothing else connects to the database after the reboot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources