Restore when there are users attached to db

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore when there are users attached to db

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2004-08-02 : 10:59:41

How do I do a restore when I have people attached to the db? I tried runnin the sp_dboption with dbo use onlt but I still get an error.

thanks.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-02 : 11:29:49

errr, you disconnect them. Here is a script to do just that. You need to call it from master. And yes, I know it uses a cursor, which is bad, bad, bad. It's been around for a couple hundred years though. I'm sure someone on here will rewrite it. :)


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




ALTER  proc uspKillDBConnections -- uspKillDBConnections 'pubs'
(
	@dbName		sysname
)
as
declare	@sqlStatement	varchar(255)
declare @spid		int

declare curUsers cursor for
	select	spid
	from 	sysprocesses
	where	dbid = db_id(@dbName)

while (select count(*) from sysprocesses where dbid = db_id(@dbName)) > 0
begin
	waitfor delay '00:00:03'
	open curUsers
	
	fetch next from curUsers into @spid
	
	while (@@fetch_status <> -1)
	begin
		select 	@sqlStatement = 'kill ' + cast(@spid as varchar)
		exec(@sqlStatement)
		
		fetch next from curUsers into @spid
	end
	
	close	curUsers
end
deallocate curUsers
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-02 : 13:00:09

Ummm, to kill users:

ALTER DATABASE DBNameGoesHere SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Then run RESTORE command.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-02 : 14:16:45

That works also. :)

Hey...I said it had been around for a LONG time. lol

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-03 : 08:20:24

Tara Will that work on MASTER?

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-03 : 12:12:53

quote:
Originally posted by JimL

Tara Will that work on MASTER?

Jim
Users <> Logic

Hmmmm....never had a need to mess with master like that....

Wht would you want to?

Unless the question is, will iot work "in" master...then the answer is yes...

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-03 : 12:28:03

Had to move an entire server to a new box with a upgraded OS.

Jim
Users <> Logic

MuadDBA

628 Posts

Posted - 2004-08-03 : 12:37:10

No, for MASTER you need to start the server in single-user mode....look it up in books online, it requires stopping the server, then running sqlserver.exe with the -m switch so only one person can connect to the whole server (not just an individual database) and then you can restore master.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources