CODEWITHSUNDEEP
filebatch-file
mvisser
mvisser

Reputation: 670

Batch files datetime

I have written a batch file. It's my first one so I still suck at this. What it does it copies a database backup file from a unc folder path and paste it to my local machine. The file name changes every night with the datetime appended to it. i.e file_backup_201306112210.bak

Below is what I have written in my batch file below

set datetimef=%date:~-4%%date:~3,2%%date:~0,2%
cmd /c echo F | xcopy "\\FileName\S$\DBs\file_backup_%datetimef%*.bak" "E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\file_backup.bak"

The weird thing is that it does work every now and then but other days it fails. I have tried to run it manually but still fails.

Can you please advise what is wrong?

Upvotes: 2

Views: 1659

Answers (2)

foxidrive
foxidrive

Reputation: 41234

Try this and look in the log file to see:
A) if the server is accessible and
B) if a file exists that matches the filespec 

@echo off
set datetimef=%date:~-4%%date:~3,2%%date:~0,2%
echo copying "file_backup_%datetimef%*.bak" on %date% at %time% >>"%userprofile%\desktop\log.txt"
dir "\\FileName\S$\DBs\file_backup_%datetimef%*.bak" >>"%userprofile%\desktop\log.txt" 2>&1

echo f|xcopy "\\FileName\S$\DBs\file_backup_%datetimef%*.bak" "E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\file_backup.bak"

Upvotes: 2

mike27015
mike27015

Reputation: 676

A bit confusing what exactly doesn't work, but there are several things you can do, for the date in your backup file name with date time:

@ECHO OFF
:: http://blog.g-ball.com/2010/08/26/year-month-day-date-batch-script/
:: Formatting the date into a YYYYMMDD format and setting it to the variable,  varTodaysDate
SET varYYYY=%DATE:~10,4%
SET varMM=%DATE:~4,2%
SET varDD=%DATE:~7,2%
SET varTodaysDate=%varYYYY%%varMM%%varDD%

If you have issues launching the batch, you might check if you are logged in when the batch is launched. Are you launching the batch file from a server management? If yes, you might check its configuration(Edit: Check with which account you run it, Run whether user is logged on or not option, and run the task, use the following user account: Administrator). If you have rights issues, I would recommand you CPAU, from http://www.joeware.net/freetools/tools/cpau/ . It will run the batch as administrator, download it and include it as following:

CPAU.exe -u 1 -p 1 -ex "cmd /c echo F | xcopy "\\FileName\S$\DBs\file_backup_%varTodaysDate%*.bak" "E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\file_backup.bak"" -wait > log.txt 2> ./logerror.txt

If you are doing xcopy from shared drives, you have to replace CPAU.exe -u username -p password, but I wouldn't recommand it, its a security vulnerability if someone gets your batch file. What I could recommand as contra, would be WinSCP with scripts to run the same functionality and copy from one directory to another one.

Upvotes: 0

Related Questions