SQLTeam.com | Weblogs | Forums

SQL Server backup timing

sql2008

#1

I have 52 GB data in my SQL Server database, Is there a way to find out how much time it will take to backup?


#2

If this is a production database, and if you are not already doing regular backups, regardless of the time it takes, the first thing you should do is take a backup.

Now that I have given that rather rude (but essential) advice, it is hard to answer your question. That is because it depends a lot of factors. How fast/slow your storage is, how much of the 52 GB is free, how busy the server is, whether you are doing a compressed backup or not and so on. So short of doing an experiment, there isn't much you can do to measure the time required.

If it will help (and I am not saying it will, because my environment is likely very different from yours) I have a 20 GB database which takes about 10 minutes to do an uncompressed full backup.


#3

If other backups are being run on that server, you can look at their size and how long they took and get a very rough estimate that way. You can also tune the backup to try to speed it up, possibly including multiple files, multiple buffers and/or maxtransfersize option.


#4

One more piece of information, if that is helpful:

When you do a backup, it does not block the database, i.e., it does not lock any tables or any other objects. So it is safe to do on a live production database. The impact should be limited to the read/write operations and resulting network traffic.


#5

We use the following code to not only get a "guestimation" of the backup time but the restore time as well.
Again...this is a GUESTIMATION. Don't rely on it to be 100% correct. You've been warned:

--Run to get all backup/restore jobs running on the server instance
SELECT r.session_id
,r.command
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
,CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0 / 60.0) AS [Elapsed Hours]
,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000), (
SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = - 1
THEN 1000
ELSE (r.statement_end_offset - r.statement_start_offset) / 2
END)
FROM sys.dm_exec_sql_text(sql_handle)
)) AS [T-SQL Code running...]
FROM sys.dm_exec_requests r
WHERE command IN (
'RESTORE DATABASE'
,'BACKUP DATABASE'
)