Automated a process to mirror a weekend tape to another.

[BoredSting]

Scenario: Maintain an onsite copy of a tape that goes offsite.

Configuration:
Server with a library and 2 drives. Due to backup window and data volume, tape raid mirroring is not viable.

Problem:
Provide a means to determine what jobs have run:
Method:
Configured (USING SQL) a trigger on the ASJOB table as follows:

CREATE TRIGGER CreateMirrors ON [dbo].[asjob]
FOR UPDATE
AS
IF UPDATE ( status ) Begin
Declare @jobstatus int
Declare @type int
set @jobstatus = (Select status from Inserted )
set @type = (Select type from Inserted )
if (( @jobstatus=1 ) or ( @jobstatus=4 ) or ( @jobstatus=3 ) ) and (@type=20) Begin
Insert into asdb.dbo.TapeMirrors
( JobID,
Status,
JobName )
Select id,
status,
comment
From Inserted
Endif
END
END

And a SQL table using the following script:

CREATE TABLE [dbo].[TapeMirrors] (
[JobID] [int] NOT NULL ,
[Status] [int] NULL ,
[Ignore] [int] NULL ,
[TapeCopied] [int] NULL ,
[TapeFormated] [int] NULL ,
[TapeMerged] [int] NULL ,
[TapeName] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MirroredTapeName] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

What these two do, is allow for me to run a script that can take apart the log files and determine the TAPE that was used in the backup job. There are two key things that are used to determine the records that need to be created. One is called type and the other is called JOBSTATUS. TYPE is used to determine if it is a rotation job. (although if you guys implemented a process to do this, then it might not need this). JOBSTATUS is used to determine if the job succeeds, fails or is incomplete.

Every time a rotation job is run it will add a record on 1, 3, and 4 (success, failure, and incomplete). It will add the JOB ID and the Name of the job. (used to help keep up with the table)

On Sunday morning the following script is run to do the ACTUALL mirroring of the tape.

I will explain each section of the code as to what they do and any assumptions made at that point.


[This Section deals with startup stuff]
Dim fso, f1, ts, s
Const ForReading = 1
Set cnDatabase = wscript.CreateObject("ADODB.Connection&quot
set rsTapes = wscript.CreateObject("ADODB.Recordset&quot
Set fso = CreateObject("Scripting.FileSystemObject&quot
set wshShell=CreateObject("Wscript.Shell&quot

strCnxn = "provider=MSDASQL;uid=backupaccount;pwd=JUNKPW;server=SQLSERVER;driver={SQL Server};database=asdb;DSN=;"
cnDatabase.Open strCnxn

[This section deals with determining jobs that need mirroring]

' Open Employees Table with a cursor that allows updates
strSQL = "Select * from TapeMirrors where Ignore=0 and TapeCopied=0 and (TapeName is Null)"
rsTapes.Open strSQL, strCnxn, 1, 3, 1

do until rsTapes.eof
' Read the contents of the file.
sInput="j" & string(7-len(cstr(rsTapes("JobID&quot)),"0&quot & cstr(rsTapes("JobID&quot)
Set ts = fspenTextFile("d:\arcserve\LOG\cas_user_logs\caroot\" & sInput & ".log", ForReading)
do
s = ts.ReadLine
if instr(1,s,"Target&quot>10 then
if instr(20,s,"-&quot=0 then
rsTapes("Ignore&quot=-1
rstapes.update
exit do
else
sTape = mid(s,instr(20,s,"-&quot-2,10)
if (instr(1,sTape,"FULL-&quot >2) or (instr(1,sTape,"ARCH-&quot >2) then
wshshell.run "net send arcservesrv" & "Found Tape " & sTape
Select Case mid(sTape,4,4)
case "FULL"
sMTape=mid(sTape,1,3) & "MIRR" & mid(sTape,8,3)
case "ARCH"
sMTape=mid(sTape,1,3) & "MIRR-04"
end select
if left(sTape,1)="D" then rsTapes("Ignore&quot=-1
rsTapes("MirroredTapeName&quot=smtape
rsTapes("TapeName&quot=stape
rstapes.update
else
rsTapes("TapeName&quot=stape
rsTapes("Ignore&quot=-1
rstapes.update
end if
exit do
end if
end if
loop
ts.Close
rsTapes.movenext
loop

rstapes.close


[This section determines the slot for the tape that needs to be mirrored onto, as it needs to be formatted]

strSQL = &quot;Select * from TapeMirrors where Ignore=0 and TapeCopied=0 and MirroredTapeName<>''&quot;
rsTapes.Open strSQL, strCnxn, 1, 3, 1
Do until rsTapes.eof
sTape=rsTapes(&quot;TapeName&quot
sMTape=rsTapes(&quot;MirroredTapeName&quot
sGroup=&quot;Group-&quot; & left(stape,2)
Set oExec = WshShell.Exec(&quot;%Comspec% /c d:\arcserve\ca_Devmgr.exe -mediainfo 2 2&quot
input = &quot;&quot;

Do until oExec.StdOut.AtEndOfStream=true
input = oExec.StdOut.Readline
if instr(1,input,smtape)>1 then
sSlot = trim(left(input,instr(1,input,&quot; &quot))
islot= cint(sslot)+1
exit do
end if
Loop

[This section actually DOES the format of the destination tape and then the tapecopy to append the sessions to it]
wshshell.run &quot;net send arcservesrv &quot; & &quot;Formatting Tape &quot; & smTape & &quot; in slot &quot; & iSlot
wshshell.run &quot;c:\winnt\system32\cmd.exe /c d:\arcserve\ca_devmgr.exe -chformat 2 2 SLOT &quot; & iSlot & &quot; &quot; & smtape , 4, true
wshshell.run &quot;net send arcservesrv &quot; & &quot;Copying Tape &quot; & sTape & &quot; to &quot; & smtape
wshshell.run &quot;c:\winnt\system32\cmd.exe /c d:\arcserve\tapecopy.exe -s&quot; & sGroup & &quot; -t&quot; & stape & &quot; -dMIRRORS -c&quot; & smtape & &quot; -a&quot;, 4,true
rsTapes(&quot;TapeCopied&quot=-1
rstapes.update
rsTapes.Movenext
loop

rstapes.close

[This section merges the new tape so it can be searched for restores]

strSQL = &quot;Select * from TapeMirrors where Ignore=0 and TapeCopied=-1 and MirroredTapeName<>'' and TapeMerged=0&quot;
rsTapes.Open strSQL, strCnxn, 1, 3, 1
Do until rsTapes.eof
sTape=rsTapes(&quot;TapeName&quot
sMTape=rsTapes(&quot;MirroredTapeName&quot
rem msgbox stape + &quot; &quot; + smtape
sGroup=&quot;Group-&quot; & left(stape,2)
wshshell.run &quot;net send arcservesrv &quot; & &quot;Submitting MERGE job for tape &quot; & smtape
wshshell.run &quot;c:\winnt\system32\cmd.exe /c d:\arcserve\ca_Merge.exe -group mirrors -allsessions -tape &quot; & smtape, 4, true
rsTapes(&quot;TapeMerged&quot=-1
rsTapes.update
rsTapes.Movenext
loop


Here are the descriptions of the different sections

§ Initial Setup of the script provides for VB Script to be able to make a necessary database connection to the SQL server that is hosting the ASDB database.
§ This section allows for the script to parse through the tape table created by the trigger and determine if the tape is a weekend job. Now there are a couple of assumptions that I am making.
a. Table format is [Device Name]-[JOB TYPE]-[CYCLE] IE B1-INCR-01. Tape names are listed below.
i. B[1-2]-INCR-[01-04] or B1-INCR-01
ii. B[1-2]-FULL-[01-03] or B1-FULL-03
iii. B[1-2]-ARCH-[01-13] or B1-ARCH-10
b. Tape format for the destination tape is B[1-2]-MIRR-[01-04]. IE B2-MIRR-04
c. Using the table, I parse through the log file to find a line TARGET and then look for the tape name on the line. The tape name is recorded and if the middle of the tape name is ARCH or FULL then I add a value for the Mirrored tape as shown in item B.
§ This section tries to determine the slot that the appropriate tape is in. This is because there is no way to tell the system to format a tape by name. A tape can only be formatted by slot. Another reason this is important, is that I wished to have the people doing restores to know they were looking at the mirrored copy of the tape. If you use TAPECOPY with out an append option, it will RENAME the tape in the process. If you use the APPEND option, then it will keep the name of the tape that was formatted.
§ This section actually formats the tape and starts TAPECOPY to copy the sessions from the source to the destination, IE B2-ARCH-04 to B2-MIRR-04.
§ This section submits a MERGE job, so that the people doing restores will be able to search and locate that tape to do the restore.

This is a pretty involved process. Here are items that will make this an easier thing to do.
§ CA_DEVMGR should allow you to format a table in a library based upon a tape name not just slot. For people who maintain tape names as I do, the naming is paramount to what they do.
§ Post job execution should allow passing variables to the post execution program (or batch file/script) so that the post execution can do things related to the script. Variables that would be nice would be
o @JOBTYPE (IE rotation, makeup, merge, etc)
o @TAPENAME (no explanation necessary)
o @TAPELOCATION (changer location for tape IE A1C1S23 for adapter 1 ID 1 slot 23)
o @BACKUPTYPE (IE Full, Incremental, Differential, etc)
o @MIRRORTAPE (Explained below, this one is not likely to happen, though)
o @STATUS (IE success, failure, ETC)
o @LOGID (Log number)
§ TAPECOPY could be integrated into the GUI to allow for automated tape copying. The features list for version 9 tout the ability to copy tapes and such. It would be nice to have a GUI ability to do just that.
§ CA_QMGR should have the ability to pull the LOG ID for a running job.

Any comments or thoughts? The process currently works, but its not that elegant a solution.