SQL Server – How to Detach a Database

To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.

There are two different methods available to detach a database from a server.

Method 1. Detach a Database using "Detach" Task in SQL Server Management Studio:

To detach a database using SQL Server Management Studio:

1. Right Click on Database you want to detach and Select "Tasks" > "Detach" Option.

image

2. Check the "Drop Connections" checkbox and click "OK" to detach the database

image

Now the database is detached from server and you can move .mdf and .ldf files related to database.

 

Method 2: Detach database using T-SQL:

To detach a database from a server using T-SQL you can use below code:

USE [master]

GO

 

ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'

GO

Result Set:

Command(s) completed successfully.

This detaches database from server.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: