automaing backups

[jes]

We copy the our database to a different folder on the same computer that it is stored each day and then copy the database onto a CD-RW each Friday.
I am wondering about automating these (perhapse using VB6 or a batch file). The PC is Windows XP Home.

I tryed the first one with a batch file. I can copy the files but the directory that I copy to has to be there allready. I know that I could use md to make directory but I would like the name to be the current date. Is there a way to do that?

[david eaton]

This can be done with a batch file. I suggest a check of these sites will tell you how to do what you want.

http://www.robvanderwoude.com/index.html

http://www.ericphelps.com/batch/samples/samples.htm

[jes]

Thanks. That worked well as soon as I spelled it right.
How can I make a folder that has the current date as the name? Then I can just copy the files from the database into that folder and they will be easy to find when we need them because they will be dated.

Here is what I have now. As you can see, I am just playing with it now.

cls
cd c:
md c:\test2\test3
copy c:\test\*.* c:\test2\test3

[david eaton]

You obviously saw my post while I was editing! Try the links I posted.
Getting the date into a foldername involves some string manipulation, but as far as I can tell it should not be impossible.

[jes]

I went through those sites and found many interesting tips but there is nothing there about naming a newly created folder with the current date.
I sent email to ericphelps.

[Paul Komski]

Which database? With MS-Access it is relatively easy to write a procedure that will backup all the relevant data tables, which (unless you are constantly changing the database structure) is all you need to backup.

Using VBA, such a procedure would be along these lines of the following code, which automatically gives each backup file a suffix using the current date.

Sub BackupTable()
    Dim strBackupName As String, strBackupPath As String
    Dim strTableName As String, strMessage As String
    
    strBackupName = "BkUp" & Format(Now, "yyyymmdd")
    strBackupPath = "C:\backup\bkup.mdb" 'Edit this value to point to a backup database
    strTableName = "Table1" 'edit appropriately or send from another module
    strMessage = "Ensure that you have a database at " & strBackupPath
    strMessage = strMessage & " or change the path and database name in the BackupTable procedure."
    strMessage = strMessage & " Click OK when you are ready to export a copy of" & strTableName
    strMessage = strMessage & " called " & strBackupName
    strMessage = strMessage & ". ANY OTHER BACKUPS CREATED TODAY WILL BE OVERWRITTEN."
    
    If MsgBox(strMessage, vbOKCancel) = vbOK Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath, acTable, strTableName, strBackupName
    End If
End Sub 

[pave_spectre]

And just for completeness sake, you could create a folder with the date using the command

md "%date%"

which will create a folder named for example, Tue 16

[david eaton]

Thanks Pave! I knew there was an easy way to do it! Having dumped all the DOS books I had, I now wish I had kept a couple.

[jes]

The database is just a collection of files in a folder in Windows. They were created and are used by a program called Info Select. Info Select is a program for organising information about our clients.
We have done all of the backups manually, but I think that we can automate the process.
I think that I can get the batch file to do all that I need it to do except for renameing the newly created file to as the current date.

I now have this:

cls
cd c:
md c:\test\"%date%"
copy c:\test\*.* c:\"%date%"

The folder is named as the day of the week and then the number of the month (monday 11). This creates a subfolder named for the number of the day (15) and a sub folder under that named for the year.
I would like the name in this case to by 15/11/2004.
Is there a way to put these in the name for the first folder or to not have them at all? Could this have something to do with my regional settings?

This is starting to work!

[pave_spectre]

Didn't notice the subfolders , obviously because the date format includes the slashes, it interprets these as directory breaks.

To resolve that you would need to alter the standard date format to somehow remove those slashes and perhaps use dashes instead.

I just wish windows had useful man pages.

Unfortunately I don't have sufficient privileges on the machine I am at to experiment with settings or commands.

[Paul Komski]

Date variables and their string manipulations are always prone to problems/headaches because of international differences in date formats (including the delimiters / or - or .). There may be also be some idiosyncrasies with WinXP and batch file syntax; syntax that I am very unfamiliar with.

The variables %YYYY% %MM% %DD% %DOW% should have produced the current year, month, day and day of week but from a command prompt under my own WinXP it shifts the variables along by one.

One would have expected that:

echo %YYYY%-%MM%-%DD% would, on today's date, produce the output 2004-11-16 but in fact it produces -2004-11

echo %MM%-%DD%-%DOW% in fact produces the desired output of 2004-11-16

Go figure: but at least you could try them out in your own country and on your own operating system to see what output it generates. If the empirical results do the job for you I guess that's all you want.

tip: if one uses the NUMERICAL format of yyyy mm dd (with whatever delimiter you like) the results will always be easy to sort chronologically

[jes]

It works well now. Thanks.

I will be puting these dated folders in another folder called backups. The backups folder will be filling up with a new subfolder once or twice a day (I haven't decided how often to schedule it yet).
Is there a way to have the batch file delete the fifth oldest when it adds a new one?

[Paul Komski]

As stated earlier batch files are not my strong point but I've put together a little VB application that I think does what you want. If nothing else you can examine the code (also in the attached txt file) and see if it gives you any other ideas on how to proceed. It automates deletion of the oldest accessed backup file but prompts you to confirm this before deletion proceeds. Using file created and file modified date attributes isn't as good since these carry on from the original file and into the backup copy. The form uses 4 text boxes and 3 command buttons but could obviously be customised in other ways.

The VB6 code:

Private Sub Command1_Click()
Dim SourceFile, DestinationFile, fs
Set fs = CreateObject("Scripting.FileSystemObject")

SourceFile = Me.Text3 & Me.Text1
If Not fs.fileexists(SourceFile) Then
    MsgBox "Source file not found"
Exit Sub
End If

If IsNull(Me.Text2) Or Me.Text2 = "" Then
    MsgBox "No destination file specified"
    Exit Sub
End If
DestinationFile = Me.Text4 & Me.Text2

If fs.fileexists(DestinationFile) Then
    If MsgBox("Destination file already exists; overwrite it?", vbOKCancel) = vbCancel Then
        Exit Sub
    End If
End If
        
FileCopy SourceFile, DestinationFile
MsgBox "File Copied to " & DestinationFile
Command3_Click
End Sub

Private Sub Command2_Click()
Dim strTemp
strTemp = Format(Date, "yyyymmdd") & Me.Text1
Me.Text2 = strTemp
End Sub

Private Sub Command3_Click()
Dim fs, f, f1, filelist()
Dim i As Integer, j As Integer, dteFileCreated As Date
Dim strFileName As String, strMessage As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\temp")

Do
    i = 0
    dteFileCreated = "1/Jan/2099"
    strMessage = "The following files exist:" & Chr(13)
    'count relevant files and id the oldest
    For Each f1 In f.Files
        If IsNumeric(Left(f1.Name, 8)) Then
            i = i + 1
            strMessage = strMessage & f1.Name & " last accessed " & f1.datelastaccessed & Chr(13)
            If f1.datelastaccessed < dteFileCreated Then 'reset if older
                dteFileCreated = f1.datelastaccessed
                strFileName = f1.Name
            End If
        End If
    Next
    If i > 5 Then
        If MsgBox(strMessage & Chr(13) & "Confirm deletion of " & strFileName, vbCritical + vbOKCancel) = vbOK Then
            ReduceFolderFiles f, strFileName
        Else
            MsgBox "deletion cancelled"
            Exit Sub
        End If
    Else
        Exit Sub
    End If
Loop

End Sub

Sub ReduceFolderFiles(Folder, FileName)
   Dim File, SubFolder
   For Each File In Folder.Files
    If File.Name = FileName Then File.Delete (True)
   Next
End Sub

[jes]

Hmmmmm. I think that I can use some of that. Thanks.

That takes care of the daily backups. Now lets look at the weekly backups. This is probably going to be a bit more complicated.
Each Friday we copy the database onto a CD-RW and store it somewere safe, like a safe.
When I right-click on a file and select the "send to" there is the option to send the file to be burned onto a cd. I think that it would be simplest to tap into this function. What do you think? Another batch file? VB6?

[Paul Komski]

Presumably you are using the XP native burner after you "send to" your CDRW drive.

The same result is achieved by copying the file or files to the
%userprofile%\Local Settings\Application Data\Microsoft\CD Burning\
folder so that just means altering you batch file's destination folder. Having got the copies to the "burning folder" I don't know of a way of initiating the burn programatically though - but having got them there its no great shakes to insert your CD and intitiate the burn from MyComputer.

EDIT
Theres a VB download which includes a readme (including a simple sub procedure) and a dll to install and register at http://www.bayden.com/delphi/cdburner.htm

Haven't tried it out but it looks straightforward enough.

[jes]

I downloaded that vb6 file and put the code it my vb6 enviroment.

Private Sub Form_Load()
Dim foo As Object ' must declare this variable in module declarations scope (not inside a sub)

Private Sub Command1_Click()
Set foo = CreateObject("BaydenBurn.XPBurn")
MsgBox ("Is XP Burning ready: " & foo.Equipped)
MsgBox ("Burn staging area: " & foo.BurnArea)
MsgBox ("Add file1: " & foo.addfile("fdsafdsa"))
MsgBox ("Add file2: " & foo.addfile("C:\try.txt"))
foo.startburn
' Do not set foo=nothing here or the wizard will vanish.
End Sub
End Sub


I would assume that it didn't work because I didn't tell it which files to burn but I don'r understand this code enough to do that.

My previous batch file just gathered files and then put them in a folder. Shouldn't this program do the same, just burn instead of copy?

[Paul Komski]

It worked OK for me (the form, project and exe in burn.zip).

Did you put the dll in your Windows/system32 folder and then register it.

Also your code doesn't look quite right - so did it compile OK?? Dim foo As Object should NOT BE IN A SUB. There should be nothing in the forms load procedure (unless you want the form to do other stuff at runtime).

Mine all appears on the form's code and looks like:

Dim foo As Object   ' at the very top in the module declarations area 
 
Private Sub Command1_Click()
Set foo = CreateObject("BaydenBurn.XPBurn")
MsgBox ("Is XP Burning ready: " & foo.Equipped)
MsgBox ("Burn staging area: " & foo.BurnArea)
MsgBox ("Add file1: " & foo.addfile("fdsafdsa"))
MsgBox ("Add file2: " & foo.addfile("C:\try.txt"))
foo.startburn
End Sub

The file fdsafdsa was not in the default directory (the one that the VB project is run from) so the msgbox returns false.

I placed a text file called try.text in the root of C (but it could be any file anywhere obviously).

After compiling, clicking on the form's button opened the WinXP CD wizard and I just needed to OK it and the burn proceeded uneventually.

Try.txt had been added by the program to the "burn staging area" which is your own user folder at
%userprofile%\Local Settings\Application Data\Microsoft\CD Burning\
so what the procedure does is to copy the files to the staging area and then opens the burning wizard.

So your batch file could do the first part and you could then open the wizard yourself. The real advantage of a small VB app would be that you could have all your procedures on one form and arranged in a more customisable way.

The zip file I uploaded earlier works quite nicely but it would need a data source to hold the default names of files and paths. As an afterthought I compiled it in a module in MS-Word and it is then very easy to change the default values.

[jes]

So, I can just replace try.txt with the actual file name (and path). I have several files to backup, how do I do that? More lines under it? Just more brackets?

[Paul Komski]

Yep - that's the idea. I would just add a line for each file (or set up a loop if you want it to look more "elegant").

[jes]

I can't say that I really understand this. I have:

Private Sub Form_Load()
Set foo = CreateObject("BaydenBurn.XPBurn")
MsgBox ("Is XP Burning ready: " & foo.Equipped)
MsgBox ("Burn staging area: " & foo.BurnArea)
MsgBox ("Add file1: " & foo.addfile("fdsafdsa"))
MsgBox ("Add file2: " & foo.addfile("C:\shared\is8working\*.*"))
foo.startburn
' Do not set foo=nothing here or the wizard will vanish.
End Sub


I get run time error 429 - activeX component can't create object.
Will these messege boxes require action by the user? I just want this to take care of itself and all that we have to do manually is put a cd rw in the drive each week.

[Paul Komski]

The message boxes and using a command button just let you control everything until you are happy that the procedure is working.

The following runs automatically for me and burns quite OK as long as there is a CD in the drive. The sendkeys statement emulates Pressing ALT+N (for the "Next" button on the Wizard) but running it once doesnt always work so repeating it a few times did the trick for me.

    
Dim foo As Object 
 
Private Sub Form_Load()
Dim n As Integer
Set foo = CreateObject("BaydenBurn.XPBurn")
foo.addfile ("C:\try.txt")
foo.startburn
For n = 1 To 10
SendKeys "%N", True
Next n
End Sub

If you are getting a runtime error then it sounds like you didnt copy the dll correctly, didn't successfully register it, or the "foo As Object" declaration is in the wrong place.

[jes]

If I make a batch file and put the line " md c:\%date% and run it, there is a folder created in my c drive that is named for the date, as it should be. But when I replace it to "md c:\%time%" the files is not named for the time. Actually, the file is not even created. I have looked at some batch file pages and it looks like I am using the correct syntax. I am just wondering if there is some other setting that I am forgetting about.

[Paul Komski]

What happens if you enter
echo %date%
and
echo %time%
at a command prompt?

[jes]

I get an error message saying that Windows XP cannot find echo.

[Paul Komski]

do you get any response in a command prompt from just echo or from echo /?

if not then I don't know the solution but it sounds as if your problem could be in this area - but strange that it digs out %date% but not %time%