How to Know Backup Speed in SQL Server? – Interview Question of the Week #277

Question: How to Know Backup Speed in SQL Server?

Answer: It is very simple to know the backup speed in SQL Server.

How to Know Backup Speed in SQL Server? - Interview Question of the Week #277 speed-800x278

You can get the answer to this question with the help of SQL Server Management Studio. Let us run the following script which is taking backup of the database.

Solarwinds
BACKUP DATABASE [AdventureWorks2017] TO
DISK = N'D:\data\adw.bak'
GO

Now when you run the above script it will give you the output response in the messages window. When you look at the window, you can see the last line which demonstrates the speed of the current backup.

How to Know Backup Speed in SQL Server? - Interview Question of the Week #277 BackupSpeed

The last line shows the efficiency in MB/sec.

It depends on many different parameters like – speed of the drive, backup options, size of the backup, and activities going on the server from where the backup is initiated. Additionally, backup also generates wait for statistics which often impacts the current server’s performance.

The best practices say one should take backup only when there are not many activities are going on the server. There are multiple types of backups – full backup, differential backup, and log backup. You will have to schedule them as per your business needs.

Here are my earlier articles on this subject, which you may find interesting.

If you have an interesting question, please share and I will be happy to blog about it with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
Where to Download SQL Server 2019 for FREE? – Interview Question of the Week #276
Next Post
How to Alter Index to Add New Columns in SQL Server? – Interview Question of the Week #278

Related Posts

Leave a Reply

Menu