Robocopy to backup SQL DB

Posted on 2004-11-29
Last Modified: 2010-04-19

I want to use robocopy.exe for the backup of two db files.  Currently the sql server keeps about 5 backups for each.

The directory layout looks like this:

so I want to copy the newest file over each night to our backup server.

thanks in advance!
Question by:gizmoadria
    LVL 95

    Expert Comment

    by:Lee W, MVP
    I'd use XCOPY instead -

    XCOPY /m *.* destination

    The /m copies files with the archive attribute on, then turns it off. only new files will have it on, so only new files will be copied.

    Author Comment

    It copied everything.

    here is my syntax:

    del S:\*.* /Q

    xcopy D:\SQL\MSSQL\BACKUP\*.* S:\ /m
    LVL 95

    Expert Comment

    by:Lee W, MVP
    Two things - did you check if the archive attribute was set on everything first?  A simply copy does not turn it off.  If it copied everything, then it has now set all the files in that folder so the attribute is off.  Any NEW file added to it since you ran XCOPY should be copied the next time you run the command.

    If you run a dir /aa it will show all files with the archive attribute.  You can all attributes for all files by running typing attrib in the directory. Any with the "A" listed should be copied the next time the xcopy command I gave is run.  At that time, it will also remove the A from those files.
    LVL 95

    Accepted Solution

    Understanding File Attributes:

    Every file and folder since the early days of DOS straight through the current versions of Windows has attributes.  There are four of them.

    A = Archive
    R = Read Only
    S = System
    H = Hidden

    The ARCHIVE attribute signals when a file needs to be archived (backed up).  Whenever you edit or create a new file - or copy an existing file to a new location, the Archive attribute is automatically turned on.  It remains this way UNTIL another program does something to change the file.  Most backup programs will remove the archive attribute.  In addition, programs like XCOPY can turn it off, and a program called "ATTRIB" can also change the attribute status.

    The READ ONLY attribute signals that this file should not be modified.  It protects the file from editing and prevents you from saving any changes to the file.  ATTRIB can toggle this on and off.  (NOTE: All files on CDs have the READ ONLY attribute turned on and it cannot be turned off).

    The SYSTEM Attribute is special and used to tell Windows that a given file or folder is intended to be used as a special file or folder in windows or DOS

    The HIDDEN attribute was far more effective in the old days of DOS, if set, attempting to do a directory listing would NOT show the file.  It would be unlisted in most views.  Today, Windows with a simple change of preferences will always show hidden files and thus this has little value today.

    If you NEVER previously ran a program that turned off the Archive Attribute, then the very first time you run the XCOPY /m source destination command will result in ALL files being copied.  Subsequent runs will result in one the new files being copied.  If you want to turn off the archive attribute for everything before you run XCOPY for the first time, then you would type the following command ONCE and never again:


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    I guess it is not common knowledge to most Wintel engineers/administrators: If you have an SNMP-based monitoring system in your environment (and it's common to have SNMP or Syslog) it's reasonably easy to enable monitoring of the Windows Event logs,…
    This may not be a text book method to resolve VSS backup issues but it seemed to have worked on few of the Windows 2003 servers we had issues while performing a Volume Shadow Copy backup. If you have issues while performing a shadow copy backup usin…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video discusses moving either the default database or any database to a new volume.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now