Automating restore. How to clear active sessions?

Question

Automating restore. How to clear active sessions?

rmaggio

SSC-Addicted

Points: 437
More actions
August 14, 2008 at 7:33 am

#191248

I have the need to weekly restore a production database to a training environment. The only thing I haven't seen yet is how to gain exclusive control to begin the restore? Is there a way to generically force the connected app servers sessions closed or killed?
I do this:
USE [master]
RESTORE DATABASE [EXP_TRAIN] FROM [filer04] WITH RESTRICTED_USER,
FILE = 1,
MOVE N'PRISTINE' TO N'H:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EXP_TRAIN.mdf',
MOVE N'PRISTINE_log' TO N'H:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EXP_TRAIN_log.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
USE [master]
GO
ALTER DATABASE [EXP_TRAIN] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
which works, but fails if there is anyone or any app server connected at the time. Is there a way to say "restore and kick everyone connected off"?
Thanks
Bob
Not a downstroke, fistpicker

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

In SQL 2000, I know we had to loop through users and KILL their processes. However I thought Ss2K5 would force users off after a short delay. The documentation specifies this, but it doesn't seem to work for me either.

rmaggio SSC-Addicted Points: 437 More actions · Answer 2

I didn't specify; it's sql server 2005.

Can I kill by user name? I won't be able to get the PID as it's different each time.

Bob
Not a downstroke, fistpicker

Ray Mond SSCertifiable Points: 7159 More actions · Answer 3

Try setting the database offline just before restoring it e.g.

USE master

ALTER DATABASE EXP_TRAIN SET OFFLINE WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [EXP_TRAIN] FROM [filer04] WITH ...

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE....

ALTER DATABASE database SET MULTI_USER;

I use the above to refresh my training environments every day.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

rmaggio SSC-Addicted Points: 437 More actions · Answer 5

Jeffrey Williams (8/14/2008)
ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE....
ALTER DATABASE database SET MULTI_USER;
I use the above to refresh my training environments every day.

Thanks Jeff! Do I have to be logged in as anything special, or will my normal full access Windows Login suffice?

Bob
Not a downstroke, fistpicker

Toby White SSCertifiable Points: 6187 More actions · Answer 6

I would tend to want to use the following becasue it addresses the fact that kill attempts might place certain processes in rollback for significant time periods. Further, if you execute a with rollback statement and the processes goes into rollback then your statement will hang for the duration.

Use Master

declare@dbName varchar(128)

select@dbName = '' ---------This must be set

declare@sql varchar(8000)

declarespidcursor cursor for

select'Kill ' + cast(spid as varchar(4))

From sysprocesses

Where db_name(dbid) = @dbName

openspidcursor

while 0 = 0

begin

Fetch next from spidcursor into @sql

if @@fetch_status <> 0 break

exec (@SQL)

--print (@SQL)

end

close spidcursor

deallocate spidcursor

If Not Exists (Select * from sysprocesses where db_name(dbid) = @dbName)

Begin

--This next part is optional you can just use any restore syntax here you want - doesn't have to be dynamic

Set @sql = 'Restore Database ' + @dbName + '

From Disk = ''Location''

with replace,recovery

,move ''logical name'' to ''physical path& Name''

'

Exec (@SQL)

End

Else

Select Status,* from sysprocesses where db_name(dbid) = @dbName -- this might reveal processes in rollback

rmaggio SSC-Addicted Points: 437 More actions · Answer 7

Thanks Toby!

The part I don't fully understand is what will be placed in rollback. This system will have maybe one connection, from a default connection pool via an app server. There shouldn't be any uncommitted transactions, and the connections will be unused and static. Weekend connection from basically a M-F training system. Would there be much of a risk of the first simple statement not working? I agree that the selection of the processes and individual kills is better(it's similar to what I'd do in an Oracle evn), but I'll only be killing one process that I've seen so far.

I can test it this weekend, as there isn't allocated disk to test the restore elsewhere on this environment.

Thanks again all!

Bob
Not a downstroke, fistpicker

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

rmaggio (8/15/2008)[hrThanks Jeff! Do I have to be logged in as anything special, or will my normal full access Windows Login suffice?

Shouldn't be a problem.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ray Mond SSCertifiable Points: 7159 More actions · Answer 9

A kill command, although returning immediately, still rolls back any transactions associated with the killed spid.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.