My question is whether there is a way to identify a backup file that is still being written to. I want to copy the completed backup files to another place. By reading the backup extension, I can't differentiate completed backup files from backup files that are still being written to. Any suggestions?
You could use
POWERSHELL and the
It will skip over files that are currently in use.
$ErrorActionPreference = 'SilentlyContinue' prevents errors from being displayed due to locked files.
powershell.exe "$ErrorActionPreference = 'SilentlyContinue';Copy-Item 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\*.bak' c:\TestOut
I think you can use procedure sp_whoisactive written by Mr. Adam Machanic, this procedure clearly shows what command is currently being executed in sql text column. if you have set of databases to be backed up, you can see which one is currently running and since you are following Ola Hallengren's script for taking backup, back ups are taking in alphabetical order meaning if DBA_Maintenance backup is currently running means database1 is already completed.
Alternatively you can use below script to get the details on percent completed and ETA completion time as below:
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.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))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
Same is explained on this link.
You may also refer Mr. Pinal Dave's page in this regard.
Hope this helps.