Hungry DBA .com for DBAs who like food

The following script can be called from a SQL Agent job to obtain the latest SQL backup from a LIVE server.

This is useful when say wanting to restore TEST from LIVE on a nightly basis

This particular version will get the latest backup stored in the Share \\LIVESERVER\SQLBackups\LIVE and copy it to the local location E:\backups\Live.bak

Dim strBackupFolder
Dim rs
Dim lMax
Dim fle
Dim fld
Dim FSO
Dim fname

strBackupFolder = "\\LIVESERVER\SQLBackups\LIVE\"
strCopyTo = "E:\backups\Live.bak"

Set FSO = CreateObject("Scripting.filesystemobject")
Set fld = FSO.GetFolder(strBackupFolder)

lMax = 0

For Each fle In fld.Files
If InStr(fle.Name, ".BAK") > 0 Then
If fle.DateLastModified > lMax Then
lMax = fle.DateLastModified
fname = fle.Name
End If
End If
Next

If fname <> "" Then
FSO.CopyFile strBackupFolder & fname, strCopyTo
End If

To call this from a SQL Agent job; create a cmd file to call the VB script, e.g.

get_latest_live_backups.cmd contains the following text:

E:\Scripts\get_latest_live_backups.vbs

Then configure the job to run the script as follows: