Scheduled Task – Xcopy does not copy file

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

xcopyDoesNotCopyNewFiles

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

RestoreMonitor-InuseFiles

 

SQL Server

sp_whoIsActive

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

sys-dm-exec-requests

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.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s