Solved

sp_detach_db

Posted on 2004-09-21
7
2,580 Views
Last Modified: 2012-08-13
Hi,
i need to detach database using osql, even if it's in use.
when detaching database from EM, window appears with option to clear current connection.
how can this be done using T-sql?

0
Comment
Question by:ill
7 Comments
 
LVL 2

Expert Comment

by:vidnan123
ID: 12110471
Use the following SQL syntax.

sp_detach_db

For example,

EXEC sp_detach_db 'pubs', 'true'

This statement detatches the pubs database with the option to not 'update statistics' after detaching the database

0
 
LVL 12

Author Comment

by:ill
ID: 12110488
i need to close acive connections first. that's the problem.
--------------
Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'rbweb2' because it is currently in use.
0
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 50 total points
ID: 12110563
for killing use

DECLARE @Proc varchar(30)

DECLARE Processes CURSOR FOR
SELECT prc.spid
FROM master.dbo.sysprocesses prc
INNER JOIN master.dbo.sysdatabases db
      ON prc.dbid = db.dbid
WHERE
      db.name = ' HERE DB NAME'

OPEN Processes
FETCH NEXT FROM Processes INTO @Proc

WHILE @@FETCH_STATUS = 0
BEGIN
      SET @Proc = 'KILL ' + @Proc
      EXEC(@Proc)
      FETCH NEXT FROM Processes INTO @Proc
END

CLOSE Processes
DEALLOCATE Processes
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 26

Accepted Solution

by:
Hilaire earned 50 total points
ID: 12110581
You could also do it as follows

alter database <dbname> set single_user with rollback immediate

<....... do your stuff here ..........>
-- also make sure to issue a "use master" before calling sp_detach

alter database <dbname> set multi_user with rollback immediate
0
 
LVL 11

Expert Comment

by:ram2098
ID: 12110584
This is what the SQL Server also internally do when you execute "clear" command..so, to script it..it is like this... (Example for PUBS database)
But, unless it is very much required. I do not recommend to query the system tables directly.


use master
GO

declare @spid int

declare curprocesses cursor
for select spid from master.dbo.sysprocesses WHERE (dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE (name = 'pubs')))

open curprocesses
fetch next from curprocesses into @spid

while (@@fetch_status = 0)
  begin
   exec ('kill ' + @spid)
   fetch next from curprocesses into @spid
  end

close curprocesses
deallocate curprocesses

EXEC sp_detach_db 'pubs'

0
 
LVL 12

Author Comment

by:ill
ID: 12110614
@oleqP thanks, it works
@Hillaire ellegant :)
0
 

Expert Comment

by:intermediagroup
ID: 28893144
if using management studio take the database offiline first.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 42
Help creating a spatial object in SQL Server 4 23
How to use three values with DATEDIFF 3 26
syntax sql error 2 14
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question