Gurus,

I am trying to create an automated restore of a database .

I've got a cursor built to stop database activity and put the database
in dbo only mode.

I've got an if...else statement to if exists drop the old database else
restore.

I run it 2x because there is always a restored copy, so the fist
statement drops it, the second restores.

This runs in query analyzer put not as a scheduled job... and I don't
know why. I've tried it in steps and all together.

Any help is appreciated. If anyone knows a better way, I'd love to hear
it. If anyone has a different way to do this that will probably take a
conversation to explain the backup schema.


I code is below. Thanks in advance

-steveb


Step1


declare @dbname varchar(30), @spid varchar(10), @start datetime
select @start = current_timestamp, @dbname = ' <mailto:'@dbname'>
@dbname'
-- Timeout after 5 mts
while(exists(Select * from sysprocesses where dbid = db_id(@dbname)) and
datediff(mi, @start, current_timestamp) < 5)


begin
declare spids cursor for
select convert(varchar, spid) from sysprocesses
where dbid = db_id(@dbname)
open spids
while(1=1)
begin
fetch spids into @spid
if @@fetch_status < 0 break
exec('kill ' + @spid)
end
deallocate spids
end
if not exists(Select * from sysprocesses where dbid = db_id(@dbname))
exec sp_dboption @dbname, 'dbo use only', true
else


begin
print 'The following processes are still using the database:'
select spid, cmd, status, last_batch, open_tran, program_name,
hostname
from sysprocesses
where dbid = db_id(@dbname)
end
go

Step2

if exists (SELECT name from sysdatabases where name = '
<mailto:'@dbname'> @dbname')
BEGIN
drop database webdata_restore
end

else
begin
restore database webdata_restore from disk = 'uncpath'

end

go

Step3

if exists (SELECT name from sysdatabases where name = '
<mailto:'@dbname'> @dbname')
BEGIN
drop database webdata_restore
end

else
begin
restore database webdata_restore from disk = 'uncpath'

end

go
MS Sql Server LazyDBA home page