Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Deattach or take offline the DB to copy Expand / Collapse
Author
Message
Posted Sunday, July 26, 2009 10:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
viji (7/26/2009)
thanks,

is there any other command just to close all the connections??

regards
viji

The command i wrote would be sufficient. the other way is to terminate all open connections using KILL.





Pradeep Singh
Post #759879
Posted Monday, July 27, 2009 2:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929
sorry,
let me be more clear
i want to close all the connections but not to take the db offline.

is there a command to do all this by one shot becoz KILL have to issue many times to kill all the processes........?

thanks
regards
viji
Post #759922
Posted Monday, July 27, 2009 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 9, 2012 6:13 AM
Points: 46, Visits: 202
ALTER DATABASE YourDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Or this will do the job. Sorry about the cursor, an old script but you get the idea. sp_ on master will make available to all dbs, bad practice, not that you seem to mind :)..

USE [master]
go

CREATE PROCEDURE sp_killprocess

@dbname varchar(128)

AS

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

declare @kill_id int
declare @query varchar(320)

declare killprocess_cursor cursor for

select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@dbname

open killprocess_cursor

fetch next from killprocess_cursor into @kill_id

while(@@fetch_status =0)
begin
set @query = 'kill '+ convert(varchar,@kill_id)
exec (@query)
fetch next from killprocess_cursor into @kill_id
end

close killprocess_cursor
deallocate killprocess_cursor


GO
Post #760000
Posted Monday, July 27, 2009 7:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
viji (7/27/2009)
i want to close all the connections but not to take the db offline.

The first two lines from the post of Victor are other two methods on how to disconnect all connections while your db will be still online. Single_user puts the database in single usesr mode(only one user can connect at a time).
If u're putting the database in restricted_users, only members of sysadmin would be able to connect to the database.





Pradeep Singh
Post #760042
Posted Monday, July 27, 2009 8:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 9:13 AM
Points: 254, Visits: 44
Hi

U need to stop the server services or else u will not be able to move ur .mdf and .ldf files
Post #760138
Posted Monday, July 27, 2009 8:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
tushaar.kudalkar (7/27/2009)
Hi

U need to stop the server services or else u will not be able to move ur .mdf and .ldf files

Do we really need to stop the services? i guess once u detach a db, the mdf and ldfs will not be in use by sql server and hence u can move the files without restarting the services.





Pradeep Singh
Post #760145
Posted Monday, July 27, 2009 10:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929

Thanks,
the suggestions helped me a lot.

regards
viji
Post #760476
Posted Monday, April 22, 2013 11:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:08 AM
Points: 12, Visits: 22
VIJI,
I am new to SQL but i am going to try your advice.

1) how stop all current transaction to that DB (let my db name be Analytic)

2) after taking it offline Just copying the Analytic.mdf and analytic.ldf to network location with click of your mouse will work? ( because I tried it doing it w/o taking it offline and gave me error: other users might be using it please stop the process and try again)


3) if yes , then I can delete current mdf and ldf file and just create a new one with same names ?

4) then bring it online and everything should work normal , Any idea of it not working when taking it online?
except the fact I lose current data in production

Please advice.

Best
Ash
Post #1445080
Posted Monday, April 22, 2013 11:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:32 PM
Points: 917, Visits: 2,861
tushaar.kudalkar (7/27/2009)
Hi

U need to stop the server services or else u will not be able to move ur .mdf and .ldf files


This is not true. The only time you would need to do ths is if there are jobs that will access this database. And, if that is true, you will only need to stop and disable the affected jobs. Stopping the service is overkill.




Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1445089
Posted Monday, April 22, 2013 12:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 42,459, Visits: 35,519
Please note: 4 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445110
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse