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!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.|||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?|||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.|||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.|||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:copy %1 "n:\networkpath\%~1 %~t1"-PatP|||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:copy %1 "n:\networkpath\%~1 %~t1"-PatP

Can you please explain the %1 and %~t1, will this append the file date?
Thanks!|||Go to the Windows XP help, and enter "Using batch parameters" (please include the quotation marks). It has a full explaination.

-PatP|||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:copy %1 "n:\networkpath\%~1 %~t1"-PatPIf 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.|||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|||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?|||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: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?|||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|||I am sure you did, and so did I, after reading this (http://www.computerhope.com/batch.htm#5)and similar pages ;)