/*kill all spids! before we start the restore process to gain 
exclusive access to the db
**SPID MUST BE LAUNCHED FROM THE RESTORE SERVER
*/
SET NOCOUNT ON

begin
declare @killspid varchar(30)

declare c_KillOtherSpid cursor for
select 'kill ' + convert(varchar, spid) as spid
from master..sysprocesses
where hostname <> '' and hostname <> @@SERVERNAME
and spid <> @@spid

open c_KillOtherSpid

fetch next from c_KillOtherSpid
into @killspid

while @@fetch_status = 0

begin

exec (@killspid)

fetch next from c_KillOtherSpid
into @killspid

end

close c_KillOtherSpid
deallocate c_KillOtherSpid

end

--find the backup file name on production
create table #bkupname (filename varchar(50))
declare @date varchar(7)
declare @cmd varchar(100)

set @date = convert(varchar,getdate(),112)
set @cmd = 'dir /b \\wmg-millbrook\G$\centricity\backup\PracticeManager_db_' + @date + '*.bak'
insert into #bkupname
exec master.dbo.xp_cmdshell @cmd

select top 1 filename into #bkupname1 from #bkupname where filename is not null
declare @filename2 varchar(50)
select @filename2 = filename from #bkupname1
drop table #bkupname
drop table #bkupname1
--select @filename

--restore that backup
declare @restdir varchar(600)
set @restdir = 'L:\' + @filename2
select @restdir
RESTORE DATABASE PracticeManager
FROM DISK = @restdir

--update the global.asa file name for the lastupdate date
DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

Create Table globalasa (
test varchar(250))
insert into globalasa
select '<!--==Visual InterDev Generated - startspan==-->' union all
select '<!--METADATA TYPE="TypeLib" NAME="Microsoft XML, v3.0" UUID="{F5078F18-C551-11D3-89B9-0000F81FE221}" VERSION="3.0"-->' union all
select '<!--METADATA TYPE="TypeLib" NAME="Microsoft Scripting Runtime" UUID="{420B2830-E718-11CF-893D-00A0C9054228}" VERSION="1.0"-->' union all
select '<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.6 Library" UUID="{00000206-0000-0010-8000-00AA006D2EA4}" VERSION="2.6"-->' union all
select '<!--==Visual InterDev Generated - endspan==-->' union all
select '<!-- #include file="version.inc"  -->' union all
select '' union all
select '<SCRIPT LANGUAGE=VBScript RUNAT=Server>' union all
select 'Sub Application_OnStart' union all
select '  SetApplicationVersion()' union all
select '	SetApplicationVariables()' union all	
select '' union all
select '	Dim ConnStr' union all
select '	ConnStr = "provider=SQLOLEDB.1;data source=" & Application("DBServer") _' union all
select '						& ";initial catalog=" & Application("DBName") _' union all
select '						& ";User Id=" & Application("DBUser") _' union all
select '						& ";Password=" & Application("DBPswd") & ";"' union all
select '	Application("DBConnString") = ConnStr' union all
select '	' union all
select '	Application("PatientExtras_AddOn") = False		' union all
select 'End Sub' union all
select '' union all
select 'Sub Application_OnEnd' union all
select '	Application.Contents.RemoveAll()' union all
select 'End Sub' union all
select '</SCRIPT>' union all
select '<SCRIPT LANGUAGE=VBScript RUNAT=Server>' union all
select '  SUB SetApplicationVariables()' union all
select '    Application("DBServer") = "WMG-PM-REPORTS"' union all
select '    Application("DBName") = "PracticeManager"' union all
select '    Application("DBUser") = "mbcuser"' union all
select '    Application("DBPswd") = "mbcuser"' union all
select '    Application("OnlineHelpURL") = "http://WMG-PM-REPORTS/CentricityPMHelp/"' union all
select '    Application("Caption") = "***REPORT SERVER - CHANGES WILL NOT BE SAVED!!*** LAST UPDATE: ' +  convert(varchar(8),getdate(),1) + '"' union all
select '  END SUB' union all
select '</SCRIPT>'

SET @FileName = 'C:\Program Files\MILLBROOK\PracticeManager\global.asa'
--SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM master..globalasa" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P sa -c'

EXEC master..xp_cmdshell @bcpCommand

drop table master..globalasa

--done