append date to osql made backup with batch

September 30, 2011 at 11:25:40
Specs: Windows XP sp3, dual 3Ghz, 2GB ram
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

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

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

@echo on

See More: append date to osql made backup with batch

Report •

October 1, 2011 at 06:39:12
I just realized that I posted the file the way I got it, with Rename in every line. It's just in the line with actual rename the output file with Today. My bad.

Report •

October 1, 2011 at 07:01:10
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%'.

Report •

October 1, 2011 at 10:32:28
I don't use Transact-SQL, but can't you just include the date logic as part of your SQL portion of the script?

How To Ask Questions The Smart Way

Report •

Related Solutions

October 1, 2011 at 11:16:42
I've been told sth like there is not possible to backup db in this version of SQL server that I have the way that is suitable for purpose that I use. If you'd lead me to desired answers with your questions, I'm on with it.The long story short: I have two versions of sql installed 8.00.2039. and 9.0.4035 Express. backup that I'm talking about is on 9th. Why do not know. Just wanna deal with appended date thing couse everything works fine the way it is. I'm not IT guy nor Have any programming preknowledge.

Report •

October 1, 2011 at 11:27:45
SQL server 2005 Express does not have a way to schedule jobs With batch I could schedule it with windows.
Thats why I need a Backup db and Append date in batch code...

Why it does't work do I know not.

Report •

October 1, 2011 at 11:48:20
I'm not talking about starting the backup from the DB, I'm talking about having the SQL client handle the date math. The Command Prompt has no real concept of "date" or "time," which leads real pain when you attempt datetime math in batch files. The obvious solution would be to see if you can do your date manipulation in SQL. This also fixes your workflow from "do something, then fix said thing," to just "do something correctly."

How To Ask Questions The Smart Way

Report •

October 1, 2011 at 13:56:22
I know nothing about Osql but is it possible that the batch script is finishing before backup is complete? Would Start /Wait OSQL -U.... etc help?

Sorry if this is not helpful.

Please come back & tell us if your problem is resolved.

Report •

October 2, 2011 at 02:24:00
Thanks for your posts I do appreciate...

\ wait osql doesn't even run it... not even backup without appended date as before did and prommt window just blinks, but is suppose to wait 30s ( -l30 )

Maybe like Razor2.3 said that have to append date before backup starts inside SQL guts, but this is a way out of my league.

Report •

October 2, 2011 at 03:17:44
I resolve it in a way...

I have many DBs to backup and rename. This I should mention earlier. My bad...

TEST.bak db in rename "C:\back\TEST.bak" "TEST%TODAY%.dat" is meant to be written just once and it would be enough for several different named DB.( I do declare all of them in the input text file of osql part though) but it's not. If I write down every single DB as TEST.bak is, then it works just fine.

I was thinking of one universal TEST like variable to work with many DBs , so I would't have to write every single one in BAT rename lines.
If you have any more ideas on that matter I'll most welcome them, anyway the matter for me is solved!

Thanks for all replys

Report •

Ask Question