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?
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With