General discussion


Backing up your database on the network

By MaryWeilage Editor ·
This week's SQL Server e-newsletter explains how to back up your database on the network. Will you use the tips described in this e-mail to back up your database to your network? If not, what method do you use? Please let us know.

If you aren't subscribed to the free SQL Server e-newsletter, click the following link to automatically sign up:

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

alternate way to backup up to network

by hughFromSA In reply to Backing up your database ...


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:\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

End if
On Error Goto 0


'WScript.Echo loopTimes
loopTimes = loopTimes - 1


set fso = nothing
set folder = nothing
set objArgs = nothing

Best regards

Related Discussions

Related Forums