1

What would be the most practical way to shut down a database to access for any user?

I need to practically shut down a specific database on a SQL Server for test purposes, and would like to know the most practical common solution.

Thanx

migrated from stackoverflow.com Jul 12 '13 at 8:12

This question came from our site for professional and enthusiast programmers.

4

You can take if offline or open as a single user.

To take it offline adapt the followin script, original at sqlauthority

-- Create Test DB
CREATE DATABASE [myDB]
GO
-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO
-- Clean up
DROP DATABASE [myDB]
GO

To make it available to a single user account original at sqlauthority:

ALTER DATABASE [YourDbName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
1

This will limit access to only current connection:

alter database [yourdbname] set single_user with rollback immediate

Rollback:

alter database [yourdbname] set multi_user

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.