Post reply

Script to copy files with current date into shared drive

  • April 6, 2010 at 4:37 am

    #213481

    Hi All,

    I am a SQL DBA but due to some requirement, I need to copy the DB backups into a shared drive on weekly basis. I am looking for a bat script which will help me do this. But i have not worked much on batch script. Hence need some help from you guys.

    The DB full backup happens on weekly basis on the local server in D drive. The retention period of these backups is 3 weeks. Once the backup for current week is done, i need to move the latest backup out of the 3 weeks backup, to the shared drive. I tried the below batch script but it copies only the most recent backup & not all the backup files with current date. Please help me correct the script.

    I even tried taking the backup directly onto shared path but somehow SQL 2008 is not recognizing the shared network path.

    @echo off

    setlocal

    set source=D:\source

    set destdir=D:\SharedDrive

    pushd "%source%"

    for /f "tokens=*" %%a in ('dir *.bak /b/od') do (set lfile=%%a)

    echo copying "%source%\%lfile%" to "%destdir%"

    copy /y "%source%\%lfile%" "%destdir%\"

    I also tried another script below but it gives me incorrect syntax error.

    @echo off

    setlocal

    set source=D:\source

    set destdir=D:\shareddrive

    echo %date% > file.txt

    for /f "tokens=*" %%a in ('file.txt') do

    copy /y "%source%\" "%destdir%\"

    Any help on this would be appreciated.

    Thanks in advance.

    ----------------------------------------------------------------
    **"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **

  • April 6, 2010 at 8:59 am

    #1146541

    Backing databases straight to a network share is not recommended. It is better to backup local and then move the backup file. There are numerous options you can use to move the files.

    1) SQL Server job using xp_cmdshell (depending on security standards, I don't use this due to security requirements that it be disabled)

    2) ROBOCOPY. There are command parameters you can use for MINAGE and MAXAGE.

    So you could do something as simple as

    robocopy "C:\backups\server1" "\\SERVER\backups\server1" /MAXAGE:1

    :exclude files older than 1 day

    3) SSIS package to backup database and then the use a file task to move the file upon successful backup.

    4) VBScripts that can move the file based on modified date. There are numerous scripts already written that you can find through a little search with Google.

    I would preference using ROBOCOPY being that for copying files over a network connection it has a resume copying feature.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply