How to show progress in a batch file using sqlcmd?

Question

I'm doing a backup of a large database in a dos batch file (not powershell), using sqlcmd (sqlcmd reference). It takes about 30 minutes.

sqlcmd -S 127.0.0.1 -d DbNameHere -E -Q "BACKUP DATABASE [DbNameHere] TO DISK = N'c:\Temp\MyBackup.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'My Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

If you run the BACKUP command in SQL Management Studio, you get the output, as it happens:

10 percent processed.
20 percent processed.
...

In a DOS batch, at best, I get all the 10,20,30..100 all appearing on the screen at once, when the backup is finished.

I've tried playing with these parameters, but I still don't get the desired progress update on screen:

-m-1
-V 1
-r1

These progress messages are buffered, and that might be part of the problem. This is dicsussed here for example: How do I flush the PRINT buffer in TSQL? But I have a single long running command, not multiple commands.

You can run a separate SQL statement elsewhere, and that tells you the progress and even the estimated finished time:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

But to use that, I would have to create a second batch file (with a sqlcmd executing this statement), make it open in a new window just before running sqlcmd backup, and run it in a loop on a 1 minute timer maybe, and end it when the backup is finished. All that in batch. Ideally, I'd rather keep it all in a single batch file! Outputting the standard progress messages would be much simpler!

Any ideas?

Answer 1

I've come up with a sort of solution. Downside is that it involves a second batchfile. It basically launches a progress window with a given name. This progress window refreshes regularly. Once the main process is finished, it calls taskkill using the given name. The progress batch relies on timeout. Both are on my Windows 8 workstation and 2008 R2 and 2012 servers.

Main backup batch:

@echo off
rem lots of other lines in my main batch file here

start "BACKUP_PROGRESS" /belownormal cmd /d /c "backup_progress2.cmd"

sqlcmd -S 127.0.0.1 -d DBNameHere-E -Q "BACKUP DATABASE [DBNameHere] TO DISK = N'c:\Temp\DBNameHere.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'DBNameHere-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

taskkill /FI "WINDOWTITLE eq BACKUP_PROGRESS" /F > nul

rem my main batch file continues here with lots of other lines

Progress batch file (called backup_progress2.cmd in the main batch). The setlocal enableextensions enabledelayedexpansion is just so that I can display the time with seconds using !time!.

@echo off
rem http://stackoverflow.com/questions/21434982/windows-batch-scripting-catch-user-reaction-to-timeout-command
SET timeout=60

:loop
cls
setlocal enableextensions enabledelayedexpansion
echo.
echo Time now: !time!
echo.
endlocal
sqlcmd -S 127.0.0.1 -d DBNameHere -E -Q "SET NOCOUNT ON;SELECT start_time,cast(percent_complete as int) as progress,dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time, cast(estimated_completion_time/1000/60 as int) as minutes_left FROM sys.dm_exec_requests r WHERE r.command='BACKUP DATABASE'"
echo.
echo Refreshing every %timeout% seconds.
timeout %timeout% > nul
goto loop