append date to osql made backup with batch

Question

thumbs up for this forum and am glad there's so many volunteers around on line.

I'm not familiar with batch, so maybe someone would be so kind to enlighten me with answer. Be very thankfull.

I try to backup many sql db with osql and then rename them with appended date ( and time)( .bak to .dat is optional too) wanna have all code in one batch so that I would be able to schedule it

Found the way to backup sql db with osql and it works fine. Also found the way to rename file in batch code and it works fine too.

But when I try to join two of them together it backups files but with no rename part. I'm stucked and seems to not find the way out of this hole. Can someone help me, please, my eyes cannot see over that obstacle.

:: BAT file

echo off

osql -U** -P** -Sserver\SQLEXPRESS2005 -ddb -u -w250 -n -l 30 -iback.txt -ooutput.txt


FOR /F "TOKENS=1,2*" %%A IN ('DATE/T') DO SET DATE=%%B
SET DATE=%DATE:/=%
FOR /F "TOKENS=*" %%A IN ('TIME/T') DO SET TIME=%%A
SET TIME=%TIME::=%
set TODAY=%DATE%%TIME%
echo %TODAY%

rename "C:\back\TEST.bak" "TEST%TODAY%.dat"

@echo on

Thanks in advance

The `rename`s should not be there, except the last one. What kind of error message do you get ? — Bo Real, Sep 30 '11 at 21:06
I got this file the way it is . First I thought to get rid of rename in every line, but I saw sth similar in several files so I left it as it is.- It doeas't work without rename in every line also. I don't get any error message just backup file with no date appended. PS: I just edit the rename. — Tim Ashley, Oct 01 '11 at 13:31
the back.txt file: " backup database ActualFileNameOfDB to disk='c:\backup\ActualFileNameOfDB.bak' " I tried to append %date% to FileName in back.txt but it just appends text '%date%'. — Tim Ashley, Oct 01 '11 at 13:56

score 0 · Answer 1 · answered Mar 22 '12 at 17:01

%DATE% and %TIME% are system environment variables and should not be reset. You should use different variables.

I like this approach:

:: BAT file

echo off

osql -U** -P** -Sserver\SQLEXPRESS2005 -ddb -u -w250 -n -l 30 -iback.txt -ooutput.txt


FOR /f "tokens=2,3,4 delims=/ " %%a in ('echo %DATE%') do (
    set "Month=%%a"
    set "Day=%%b"
    set "Year=%%c"
    )
FOR /f "tokens=1,2,3,4 delims=:." %%a in ('echo %TIME%') do (
    set "HOUR=%%a"
    set "MINUTE=%%b"
    set "SECOND=%%c"
    set "HUNDREDTHS=%%d"
    )   

SET Today=%YEAR%%MONTH%%DAY%%HOUR%%MINUTE%%SECOND%%HUNDREDTHS%
echo %TODAY%

rename "C:\back\TEST.bak" "TEST%TODAY%.dat"

@echo on

append date to osql made backup with batch

1 Answers1