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?
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.
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.
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.
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'
)