SQL Server

SQL Server – DATABASE Backup in terms of Percent_Complete

- by Prince Rastogi - 9 Comments.

Hi Friends,

Here I am explaining  a very general scenario about database backup.

Problem :

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.

Solution :

When we take the backup from Management Studio then it shows the amount of percentage completed in the bottom – left corner progress section.

1_SQL_Server_DATABASE_Backup_in_terms_of_Percent_Complete

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..

2_SQL_Server_DATABASE_Backup_in_terms_of_Percent_Complete

   

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…

3_SQL_Server_DATABASE_Backup_in_terms_of_Percent_Complete

 

Regards

Prince Rastogi

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

   

Related Posts

SQL Server Query Cost, Memory Grant & SQLRESERVATIONS Clerk

SQL Server Memory Troubleshooting

Performance Monitor Counters

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

9 Comments on “SQL Server – DATABASE Backup in terms of Percent_Complete”

  1. 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.

    HTH

    Reply

  4. You can also specify STATS = [Values] eg: STATS=5 progress will be shown in messages windows upon completion of every 5 percent.

    Reply

  5. if database backup is running by scheduled job , in this case how to see the percent_complete info?

    Reply

  6. Hi Navaratan,

    Thanks, For your suggestion!!

    Hi Yogendra,

    This query also works, if you take backup through maintenance Plan scheduled Jobs.

    Thanks & Regards:

    PRINCE KUMAR RASTOGI

    Reply

  7. 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 🙂

    Reply

  8. Hi user273228,

    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

    Reply

  9. 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.

    Reply

Leave a Reply

Your email address will not be published.