generate backup file using date and time as filename

Question

How do I do this properly. I'm trying to name the sql file that is being produced by mysqldump into the current date and time. I've already some research in this site and found a code in here: How to get current datetime on Windows command line, in a suitable format for using in a filename?

Tried to mixed it up with my current code and I came up with this one. The file is named into the current date and time but its only a 1kb file and does not produce a .sql file. It is supposed to be a 7 kb sql file.

@For /f "tokens=2-4 delims=/ " %%a in ('date /t') do @(set mydate=%%c-%%a-%%b)
@For /f "tokens=1-2 delims=/:" %%a in ('time /t') do @(set mytime=%%a%%b)    

@echo mydate= %mydate%
@echo mytime= %mytime%

mysqldump -u root -p --add-drop-table --create-options --password= onstor >c:\%mydate%_%mytime%.sql

UPDATE I don't think there's a problem with the mysqldump command since it works well when I do it this way. The code below just uses the date as its filename.

@For /F "tokens=2,3,4 delims=/ " %%A in ('Date /t') do @( 
    Set Month=%%A
    Set Day=%%B
    Set Year=%%C   
)    

@echo DAY = %Day%
@echo Month = %Month%
@echo Year = %Year%    

mysqldump -u root --add-drop-table --create-options --password= onstor >c:\%Day%-%Month%-%Year%.sql

Please help, thanks.

Answer 1

On Linux, simply put $(date +%Y-%m-%d-%H.%M.%S) to show date and time in the file name, so it looks like:

mysqldump -u <user> -p <database> | bzip2 -c > <backup>$(date +%Y-%m-%d-%H.%M.%S).sql.bz2 

(This command also compresses the file using bzip2)

Answer 2

I think the syntax of your mysqldump command is wrong;

mysqldump -u root -p --add-drop-table --create-options --password= onstor

You use both -p and --pasword=, you should only use one option. And there is a space before the password.

Just try to run the mysqldump command on the commandline to see error messages. Alternatively add 2>&1 at the end of the command in the batchfile. Then you would also see error messages in the output file.

mysqldump -u root --add-drop-table --create-options --password=onstor >c:\%mydate%_%mytime%.sql 2>&1