Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: batch file to copy file and append date

    The Sql Server database can only see the local drive.

    I would like to set up a batch file that will copy a SQL Server

    backup file from the local drive to the network drive. I would

    like to append the file date to the end of the copied file. I

    assume a batch file can accomplish this but I am new to batch

    file writing. Does anyone have code that they already created

    for this sort of task??

    Thank you!

  2. #2
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do you plan to do this?

    From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

    I've heard of people using robocopy...what do you want to use? ftp?

    Just a plain copy command?

    And from where a sproc, SQL Server agent Job?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by Brett Kaiser
    How do you plan to do this?

    From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

    I've heard of people using robocopy...what do you want to use? ftp?

    Just a plain copy command?

    And from where a sproc, SQL Server agent Job?
    I just want a plain copy within a batch file, but one that can append the file date (ie BackupFile_mm_dd_yyyy). Currently this copy has been set up within windows Task scheduler. I want to do the same but run the batch file in the task. Fyi: Because SQL Server cannot see the network, the task was set up with Windows tasks scheduler.

    Let me know your thoughts..

    Thank you.

  5. #5
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by pshisbey
    You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.
    Thank you for your suggestion. I have not worked with VB Script so was hoping to accomplish this within a batch file. But thank you for your response. I will look into this as another option.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:
    Code:
    copy %1 "n:\networkpath\%~1 %~t1"
    -PatP

  7. #7
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by Pat Phelan
    If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:
    Code:
    copy %1 "n:\networkpath\%~1 %~t1"
    -PatP
    Can you please explain the %1 and %~t1, will this append the file date?
    Thanks!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Go to the Windows XP help, and enter "Using batch parameters" (please include the quotation marks). It has a full explaination.

    -PatP

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:
    Code:
    copy %1 "n:\networkpath\%~1 %~t1"
    -PatP
    If the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    I have a job which save databases structure into files. It create directory 'year-month-day' and dump structure there. This is code:
    -------------------------------
    DECLARE @command varchar(1000);
    -- create local directory for dump
    SET @command='mkdir C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
    exec xp_cmdshell @command;

    --create dump
    SET @command='xp_cmdshell ''"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s TESTERS /d ? /P 1 /f C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+'\?.sql''';
    exec sp_MSforeachdb
    @command1 = @command,
    @replacechar = '?'

    --create remote dir
    SET @command='mkdir \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
    exec xp_cmdshell @command;

    --copy databases structure to remote computer
    SET @command='copy '+'C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+' \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
    exec xp_cmdshell @command;
    -------------------------------
    I hope this help you

  11. #11
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by rdjabarov
    If the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.
    Can you suggest how to append mmddyyyy to end of filename in a backup command within a SQL SERVER job?

  12. #12
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by Pat Phelan
    If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:
    Code:
    copy %1 "n:\networkpath\%~1 %~t1"
    -PatP
    The results is close but beacuse of the slashes in the date, the output is going into subdirectories not a single filename. Any suggestions?

  13. #13
    Join Date
    Oct 2003
    Posts
    12

    Smile Solved

    I figured out how to copy a file and append a date to the copy. Here is the code for the batch file:

    :: COPY FILE AND DATE
    ::
    @ECHO OFF

    FOR /F "tokens=2,3,4 delims=/ " %%a IN ('DATE /t') DO SET mydate=_%%a_%%b_%%c

    ::ECHO The value is "%mydate%"
    copy "c:\readme" "L:\DATABASES\PROJECT REVIEW\readme%mydate%" /Y

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I am sure you did, and so did I, after reading this and similar pages
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •