CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
radbyx
radbyx

Reputation: 9670

How to force a SQL Server 2008 database to go Offline

How do I force my Database to go Offline, without regard to what or who is already using it?

I tried:

ALTER DATABASE database-name SET OFFLINE;

But it's still hanging after 7 min.

I want this because I need to test the scenario.

If it's even possible?

Upvotes: 118

Views: 243752

Answers (2)

abatishchev
abatishchev

Reputation: 100308

Go offline

USE master
GO
ALTER DATABASE YourDatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Go online

USE master
GO
ALTER DATABASE YourDatabaseName
SET ONLINE
GO

Upvotes: 203

Martin Smith
Martin Smith

Reputation: 453608

You need to use WITH ROLLBACK IMMEDIATE to boot other conections out with no regards to what or who is is already using it.

Or use WITH NO_WAIT to not hang and not kill existing connections. See http://www.blackwasp.co.uk/SQLOffline.aspx for details

Upvotes: 28

Related Questions