asked on

call multiple sqlcmd's within one bat file - with logging

I need to enable maintenance jobs on two SQL Server Express instances. I have created the bat files calling the necessary procedures, but I need some help wrapping up the routine for execution via a Windows Scheduled task.

I have these 6 bat files -

prod_userdb_integrityChk.bat
prod_userdb_indexOptimize.bat
prod_userdb_bkup.bat
prod_sysdb_integrityChk.bat
prod_sysdb_indexOptimize.bat
prod_sysdb_bkup.bat

They all follow the same format, here is the entire content of prod_sysdb_bkup.bat:

sqlcmd -E -S PROD\INSTANCE -d msdb -Q "EXECUTE dbo.DatabaseBackup @Databases = 'SYSTEM_DATABASES', @Directory = '\\backup_share\', @CleanupTime = 72, @BackupType = 'FULL'" -b -o C:\Logs\prod_sysdb_bkup.txt

Each one of them calls a procedure, performs an action, and creates and output file. I want to combine the three sys db bats into one, and the three user db bats into another, and then call those combined bats from the Windows Scheduled task. For example, it would be the 'sys_db_maint.bat' and 'user_db_maint.bat' below that are called in a windows scheduled task:

sys_db_maint.bat - executes these three in sequence:
prod_sysdb_integrityChk.bat
prod_sysdb_indexOptimize.bat
prod_sysdb_bkup.bat

user_db_maint.bat - executes these three in sequence:
prod_userdb_integrityChk.bat
prod_userdb_indexOptimize.bat
prod_userdb_bkup.bat

Ideally, the logs from each of the internal bat files would somehow be appended so that I have one log for the sys_db_maint.bat execution, and one log for the user_db_maint.bat execution.

I also need a datestamp on the final output log, something like this:

sys_db_maint_20161024_230030.txt
user_db_maint_20161024_231159.txt

Batch file scripting is not my strength, and I need some Expert assistance, please.

David Johnson, CD

I don't know whether or not tsql supports append for output. but one can always concatenate the files together into a 3rd file
create another batch file

FOR /f "tokens=1-8 delims=:./ " %%G IN ("%date%_%time%") DO (
SET datetime=%%G%%H%%I_%%J_%%K
)
copy prod_sysdb_bkup.txt + file2.txt + file3.txt "system %datetime%".txt"
del prod_sysdb_bkup.txt
del file2.txt
del file3.txt

Open in new window

dbaSQL

ASKER

Thank you, David. As I said, bat file scripting is not one of my strengths, and unfortunately, it is not clear to me where I am putting the statement you've provided. I could be wrong, but i believe your statement concatenates the log files after they've been written to disk. Before I can do that, however, I need to create the sys_db_maint.bat, which calls each of these:

prod_sysdb_integrityChk.bat
prod_sysdb_indexOptimize.bat
prod_sysdb_bkup.bat

And then the user_db_maint.bat, which calls each of these:

prod_userdb_integrityChk.bat
prod_userdb_indexOptimize.bat
prod_userdb_bkup.bat

Can you show me the correct way to call three bats within one bat, in sequence, and with a date stamped log of the execution?

dbaSQL

ASKER

This may do it:
https://www.experts-exchange.com/questions/28690617/Run-multiple-sql-scripts-using-either-a-Powershell-or-BAT-script.html

Possibly with your concatenation included. No?

David Johnson, CD

FOR /f "tokens=1-8 delims=:./ " %%G IN ("%date%_%time%") DO (
SET datetime=%%G%%H%%I_%%J_%%K
)
call prod_sysdb_integrityChk.bat
call  prod_sysdb_indexOptimize.bat
call prod_sysdb_bkup.bat
copy prod_sysdb_bkup.txt + file2.txt + file3.txt "system %datetime%".txt"
del prod_sysdb_bkup.txt
del file2.txt
del file3.txt

Open in new window

Adjust as required

dbaSQL

ASKER

Very good, David. Thank you! I must step out briefly, but I will be testing it later today and will let you know.

dbaSQL

ASKER

Hi David. I wanted to let you know that I haven't been able to test the whole thing yet -- only the individual bat files. I am waiting for a new service account so I can test the backup.bat, and the one that calls the three bats together. I will get an update to you as soon as I am able.

ASKER CERTIFIED SOLUTION

David Johnson, CD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dbaSQL

ASKER

I still haven't been able to test it, David, but I don't want to keep this open so long. I will post follow up in this ticket as soon as it's done. Thank you for your help!

dbaSQL

ASKER

David, it worked great!! First try, no errors -- processed through the three bats -- integrity check, index, backup -- generated each out and merged them all into one, and deleted the outs. Everything good. Except the final output filename --

system Thu1103_2016_12_46

Can you help me change that datestamp to this: YYYYMMDDhhmmss
Or even just YYYYMMDD