All,
I'm a dingbat!

I left out the "with move" portion of my restore. It was erroring out
looking for a valid file location to write the restore.

Sorry for the hassle

-steveb

-----Original Message-----
From: Bowman, Steve
Sent: Monday, July 28, 2003 11:12 AM
To: LazyDBA.com Discussion
Subject: RE: auto restoration as a job.


The scheduled job in Ent Mgr shows a red x, when I check the sql server
logs I don't see any record of this transaction happening. When I check
the even log all I see is a message that the job has failed.

-steveb

-----Original Message-----
From: Kevin Martin [mailto:kevin.[Email Address Removed]
Sent: Monday, July 28, 2003 10:54 AM
To: LazyDBA.com Discussion
Subject: RE: auto restoration as a job.


Can you describe the failure? What message are you getting? Kevin
Martin Database Engineer Multimedia Games



-----Original Message-----
From: Bowman, Steve [mailto:[Email Address Removed] Monday, July 28, 2003 9:51 AM
To: LazyDBA.com Discussion
Subject: auto restoration as a job.


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

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 7/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 7/14/2003


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page