Here I am explaining a very general scenario about database backup.
Suppose if we have a large database. The Backup process of this database will take long time but we just want to know how much percent of that backup is completed at the time of Backup process is running.
When we take the backup from Management Studio then it shows the amount of percentage completed in the bottom – left corner progress section.
But what happen, when we take the backup of the database using TSQL.
I have a database GEEKS of 10 GB. When we execute the backup command as mention below..
BACKUP DATABASE GEEKS TO DISK='M:\DATABASE\GEEKS.bak'
Then SSMS shows no information about how much backup process is completed. As shown below..
To know such type of information microsoft provides a special type of DMV “sys.dm_exec_requests”. This DMV provides many columns data to show various information. here we just want to know only the value of column “percent_complete”. So we can execute this command..
select percent_complete,* from sys.dm_exec_requests where command='BACKUP DATABASE'
Output of this query is shown below…
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
9 Comments on “SQL Server – DATABASE Backup in terms of Percent_Complete”
select percent_complete, dateadd(ms,estimated_completion_time,getdate()) from sys.dm_exec_requests where command like ‘backup%’
this will give you percent completed and also the estimated time in human readable format.
Thanks for your Suggestion, Naginder!
You can also specify STATS = [Values] eg: STATS=5 progress will be shown in messages windows upon completion of every 5 percent.
if database backup is running by scheduled job , in this case how to see the percent_complete info?
Thanks, For your suggestion!!
This query also works, if you take backup through maintenance Plan scheduled Jobs.
Thanks & Regards:
PRINCE KUMAR RASTOGI
Quote: “Then SSMS shows no information about how much backup process is completed. As shown below.”
Look at the “Messages” tab, that will show you the progress 🙂
Yes, you are right. But not informed us about how much percent completed, here we are talking about percent completed. and the meaning of that “quote” is shows no information about percent completed.
prince kumar rastogi
ok I see Navratan has already suggested this.
BACKUP DATABASE GEEKS TO DISK=’M:\DATABASE\GEEKS.bak’ WITH STATS=25
Above will show the progress under “messages” tab every 25% completed. More importantly it also shows you MB/sec and # of pages processed.