Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bogey
Posting Yak Master
166 Posts |
 Posted - 2004-09-09 : 09:44:07
|
| I've searched the forum but I'm not sure if I'm getting anywhere with this. We run multiple databases that are not very large but are important. I run nightly backups with transaction logs every 30 minutes. We've had a mishap and some data was deleted. I need to restore the db and apply transaction logs. My question is I need exclusive access to db for the restore. Do I need to detach the db to do this or can I kick eveyone off and keep them off through T-Sql?Thanks. |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-09-09 : 09:48:55
|
| Kill the connections (or get them to log off) then start the restore. Noone will be able to log on whilst the restore takes place.-------Moo. :) |
 |
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2004-09-09 : 09:52:25
|
| I could have anywhere from 1 - 50 connections at any given time. How to I kill "ALL" of the connections and keep them off? |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-09-09 : 10:02:53
|
| Something like this maybe:create your admin.dbo.whom_results table 1st then do this:[CODE]insert into Admin.dbo.whom_results execute sp_whomdeclare @spid varchar(10)declare @sql varchar(15)declare tokill cursor for select SPID from admin.dbo.whom_results where not Login LIKE '%sql7dw' and not Login LIKE '%sqldwh' and not Login = 'sa' and not DBName = 'master' and not DBName = 'msdb'open tokillfetch next from tokill into @spid print 'SPID Killed: ' while @@fetch_status = 0 begin set @sql = 'kill ' + @spid execute (@sql) print @spid fetch next from tokill into @spid endclose tokilldeallocate tokill[/CODE]edit: and the code for sp_whom is downloadable from various places on the net(in case you don't have it already) - google itDuane. |
|
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2004-09-09 : 10:34:07
|
| Is this the best way to do this. What happens if your at a customers site who does not have access to the net and your doing a restore? Is detaching the db a good alternative?I do appreciate your quick response duane. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-09-09 : 11:05:48
|
| If you download the sp_whom code you can keep it on a removable disk and install it at the customers site.edit: sp_who not whoM is a standard sp in sql so you could also use this or look at it's code to determine who is logged on and use it accordingly - to kill the processesDuane. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-09 : 15:33:07
|
| Setting the database to DBO only, or single user, or readonly might help too - i.e. stop anyone new logging on whilst you turf the existing users off, and then noone new can log in during the interval between throwing-off and restoring-databaseKristen |
|
|
|
|
|
|
Need Exclusive Access for recovery