I want to run a command line script using taskscheduler for backing up all my databases and have them named by the date and time they were backed up. I trawled the internet for some background info and hit upon the script below. The idea is that you supply the server name, destination directory and user credentials and call the script from a bat file. I keep getting an error Incorrect systax near 'exec sp_msforeachdb
Can you cast your expert eyes over this script nd let me know what needs to be corrected.
Thanks in advance,
for /f "tokens=2" %%d in ('echo %date%') do (
for /f "tokens=1-3 delims=/" %%j in ('echo %%d') do (
set /a day=%day%-1
if %day% lss 10 (
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a%%b
sqlcmd -S %SERVER% -U sa -P xxxxxxxx -d master -Q "exec sp_msforeachdb 'BACKUP DATABASE [?] TO DISK=''%BACKUP_DIR%\?.Full.%mydate%.%XTIME%.bak'''