Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to show progress in a batch file using sqlcmd?

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?

like image 680
Thierry_S Avatar asked Jul 07 '15 09:07

Thierry_S


People also ask

How to check SQL backup progress?

Open SSMS, right click on a database then select Tasks > Back Up. A screen similar to the below image will open. After you select all of the backup options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the backup.

How to check database restore time in SQL Server?

We get the following database restoration history in my environment. restore_date: It shows the database restoration date. destination_database_name: We can get the destination database name using this column. user_name: it gives user name that performed the restoration for that particular database.

What is Sqlcmd command?

The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. To use sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL.

How to check backup schedule in SQL Server?

Click Connect. In Object Explorer, expand Databases. Right-click the database that you want to back up, click Tasks, and then click Back Up. In the Back Up Database - DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.


1 Answers

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
like image 148
Thierry_S Avatar answered Sep 24 '22 19:09

Thierry_S