Earlier today I was tidying up some scripts that are part of a large, multi-database merge project I am working on. I set out to automate this as much as possible as it will need to be performed numerous times as data refreshes are needed and we move closer to transitioning from 6 groups of databases (49 in total) to the new merged set of databases (6 in total).
The very first set of steps in the process consists of restoring databases from full backups that are pushed to SQL Server 2005 Cluster. The database backup files can take some time making their way from the origin server to the file system on the SQL cluster. At some point, the process of running the merge routines will become automated and scheduled, and I now have an easy way (right within T-SQL) to check if the files are present before attempting to use them.
If no ouput parameter is specified (OUT), the value returned is a table containing three columns, each of type INT: [File Exists] , [File is a Directory] , [Parent Directory Exists].
File Exists - 1 Yes, 0 No
File is a Directory - 1 Yes, 0 No
Parent Directory Exists - 1 Yes, 0 No
I have a temporary table that is populated with various settings and values for each group of databases being merged together. I simply select the names of the database backup files from my temporary table and use a cursor to loop over the recordset. Inside this loop is where I make use of xp_fileExist. I set a flag in my temporary table when the file does exist and proceed with the next step.
I realize that while I may be able to determine if the file exists, directory exists, or parent exists, I am not able to easily obtain a size value for the file. I will address that in a later post. For now, give the example code below a whirl and see if you have a need in your T-SQL code for such a procedure.
SET NOCOUNT ON
CREATE TABLE #files
INSERT INTO #files
EXEC xp_fileExist 'C:\temp'
DROP TABLE #files
SET NOCOUNT OFF