Hi,
I've used MSSQL to back up to a network share, but the risk is if there is a network hiccup, then the backup will fail. My best option then was to first make a local backup and then move it to the network share. The moving part was the tricky part though. I wrote a small .vbs file to move the files. The vbs file takes 3 arguments, source, destination, and loop-times. The reason for argument-3, is if there is a network hiccup, it should just try to move the file again.
What I do then is, I have a .bat file which calls the .vbs files, and it sends over the arguments. This .bat file is scheduled in Windows Scheduler (I guess you can schedule a SQL job to run it also) to run every 2 hours (give enough time for the first move-schedule to complete) (to start when the SQL backups are completed) - because sometimes, trying to move a big file will fail through all the loop attempts.
Currently this system is running smooth for about 2 months. All the backup files are moved within the first 3 scheduled attempts.
The .bat file looks like this:
d:
cd\
d:\backups\move.vbs d:\backups\data\master\ \\backupServer\backups\data\distribution\ 4
d:\backups\move.vbs d:\backups\data\db1\ \\backupServer\backups\data\db1\ 4
d:\backups\move.vbs d:\backups\data\db2\ \\backupServer\backups\data\db2\ 4
d:\backups\move.vbs d:\backups\data\db3\ \\backupServer\backups\data\db3\ 4
and the .vbs file is:
Option Explicit
Dim fso
Dim Folder
Dim Files
Dim File
Dim objArgs
Dim sourceDir
Dim destDir
Dim loopTimes
Set objArgs = WScript.Arguments
sourceDir = objArgs(0)
destDir = objArgs(1)
loopTimes = objArgs(2)
Set fso = CreateObject("Scripting.FileSystemObject")
do while (loopTimes > 0)
Set Folder = fso.GetFolder(sourceDir)
Set Files = Folder.Files
For Each File In Files
On Error resume next
File.Copy (destDir)
If err <> 0 Then
else
File.Delete
End if
On Error Goto 0
Next
'WScript.Echo loopTimes
loopTimes = loopTimes - 1
loop
set fso = nothing
set folder = nothing
set objArgs = nothing
Best regards
Hugh
