SQL Server: How to Check the Status of a File Before Processing

April 18, 2005

SQL Server Database administrators often copy(refer Fig 1.0) huge files, such as Full backup files and transaction log backup files from production to QA or from production to development environment and so on. Sometimes they need to copy source data files for importing. If they want to restore those Full backups or transaction log backup files or import those huge source files, they have to wait until the copy is complete.

In SQL Server there are three way to check the status of the file. They are:

However, when a file is being copied (refer Fig 1.0), the above three statements report that the file exists and is ready to be used, which is false. Therefore, in order to discover the real status of the file we have to create a user defined function as shown below. This Function takes advantage of SP_Oamethod and the File system object to get the real status of the File.