• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Robocopy to backup SQL DB


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:
dbfile
dbfile_db_200410292000.bak
dbfile_db_200410302000.bak
dbfile_db_200411012000.bak

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

thanks in advance!
0
gizmoadria
Asked:
gizmoadria
  • 3
1 Solution
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
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.
0
 
gizmoadriaAuthor Commented:
It copied everything.

here is my syntax:

del S:\*.* /Q

xcopy D:\SQL\MSSQL\BACKUP\*.* S:\ /m
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
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.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
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:

ATTRIB -A D:\SQL\MSSQL\BACKUP\*.*
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now