Background
We have a scheduled script that was not working as new files were not being copied over.
But, also there were no errors logged.
Script – Backup
Here is what the original script looks like.
Script – Backup ( Original )
set "app=xcopy"
set "_HOME_DIR=%CD%"
set "_DATABASE=DBBackup"
set "_srcFolder=\\DBPROD\SQLBackups\%_DATABASE%\%_DATABASE%.bak"
set "_destFolder=F:\Microsoft\SQLServer\SQLRestore"
@rem if Folder Log does not exist, please create it
if not exist log mkdir log
@rem Initiate xcopy
%app% %_srcFolder% %_destFolder% /D /Y /J
Script – Backup ( Revision )
Added code to capture and expose ERRORLEVEL.
set "app=xcopy"
set "_DATABASE=HRDB"
set "_srcFolder=\\DBPROD\SQLBackups\%_DATABASE%\%_DATABASE%.bak"
set "_destFolder=F:\Microsoft\SQLServer\SQLRestore"
@rem if Folder Log does not exist, please create it
if not exist log mkdir log
@rem Initiate xcopy
%app% %_srcFolder% %_destFolder% /D /Y /J
set _errLevel=%errorlevel%
if _errLevel neq 0 (
echo _errLevel %_errLevel%
echo ErrorLevel %errorLevel%
exit /b %_errLevel%
)
Output
Image

Text
D:\Scripts\RestoreDB>xcopy \\DBPROD\SQLBackups\HRDB\HRDB.bak F:\Microsoft\SQLServer\SQLRestore /D /Y /J
\\DBPROD\SQLBackups\HRDB\HRDB.bak
Sharing violation
D:\Scripts\RestoreDB>set _errLevel=4
D:\Scripts\RestoreDB>if _errLevel NEQ 0 (
echo _errLevel 4
echo ErrorLevel 0
exit /b 4
)
_errLevel 4
ErrorLevel 0
D:\Scripts\RestoreDB>set _errLevel=4
D:\Scripts\RestoreDB>if _errLevel NEQ 0 (
echo restoreDB_DBBackup::_errLevel 4
echo restoreDB_DBBackup::ErrorLevel 0
exit /b 4
)
Image
We are experiencing a sharing violation.
Diagnostics
Who is using the file?
Resource Monitor
In use files

SQL Server
sp_whoIsActive

sys.dm_exec_requests
Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
Answered By – Veldmuis
Link
Code
SELECT
r.session_id
,r.command
,[Percent Complete]
= CONVERT(NUMERIC(6,2),r.percent_complete)
, [ETA Completion Time]
= CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20)
, [Elapsed Min]
= CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0)
, [ETA Min]
= CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0)
, [ETA Hours]
= CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0)
, [sqlText]
= 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')
Output

Summary
Our problem is that a prior run has the file we are trying to copy via xcopy locked.
We can wait for the database restore to complete, or terminate it.
Also, xcopy with /D works fine … just make sure that you check the ERRORLEVEL.