Esat Erkec
dir command in MS-DOS

File Validation in SQL Server with xp_fileexist

July 6, 2021 by

In this article, we will explore the xp_fileexist extended stored procedure usage and other details. At the same time, we will take a look at alternative methods to this procedure.

Introduction

In some cases, database administrators may need to verify whether a particular file exists in a particular folder through SQL Server. For example, we need this function to check whether the backup file in the backup folder before starting the backup process. The biggest disadvantage of this procedure is undocumented for this reason we need to test it properly before deciding to use it in the production database environments.

Syntax

The syntax of the xp_fileexist SP is as follow:

filename: This argument specifies the file path and file name that needs to be validated.

file_exists: This is an optional output parameter and returns information whether the file is in the specified directory. The 0 value indicates the file does not exist and the 1 value indicates the file does exist.

For example, the following query validates whether there is a tempdb database MDF file under the specified folder. The CASE statement converts the output result of the procedure into a meaningful text. When the result is 1 it returns “File Exists” text otherwise it will return “File Not Exists”.

Checking the existence of a file in SQL Server

At the same time, we can print the result of the extended procedure into the message tab with help of the following query.

Checking the existence of a file with a query.

Advanced usage

On the other hand, the xp_fileexist extended procedure returns a dataset. When we execute the following query the File Exists and Parent Directory Exists column values will be 1.

Interpreting the File Exist column

This result shows that the file and directory are valid. When we change the tempdb file extension only the Parent Directory Exists column will show 1.

Interpreting the Parent Directory Exists

The result set shows that the path exists but the file is not found in this path.

Checking the existence of a particular directory

Through the xp_fileexist procedure, we can verify whether a directory exists or not exists in the file system. The main difference between the previous usage is we pass the directory path only to the filename parameter. For example, the following query checks if the error log file exists for the SQL Server instance.

Checking the existence of a particular directory with a query

As we can see the File is a Directory column that indicates the searched directory exists.

  • Tip: The following query helps to find out the error log file location in SQL Server

How to find error log file directory in SQL Server with a query

When we run the procedure for the non-exist file path only the Parent Directory Exists column returns 1 because the specified file path up-level directory exists in the file system.

How to verify a directory that exists in a file system with a query

Alternative-1 : sys.dm_os_file_exists

With SQL Server 2017 a new DMF (Dynamic Management Function) has been introduced and this function has the same capabilities as the xp_fileexists procedure but the usage syntax is a bit different from it.

Usage details of the sys.dm_os_file_exists dynamic management function

As we can see, there is no difference in the result set between sys.dm_os_file_exists and xp_fileexists.The main advantage of the sys.dm_os_file_exists DMF is, we can easily join this function with the other tables.

Alternative-2 : xp_cmdshell

The xp_cmdshell is a very powerful extended stored procedure and is used to execute operating system commands from the SQL Server. In the default settings, the xp_cmdshell procedure is disabled to usage. For this reason, we need to enable this option to use the sp_configure. The following query will enable the xp_cmdshell option.

Now, we check the status of the xp_cmdshell option and we have to see run_value as 1 for the proper configuration of the xp_cmdshell.

Enabling  xp_cmdshell procedure

The dir is an MS-DOS command that gives information about the files and directories. So, we can use a very basic dir command to check the existence of a particular file but we can not directly execute an MS-DOS command from SQL Server. Therefore, we will execute this command with help of the xp_cmdshell procedure. After executing the query we will tackle it line by line.

Checking the existence of a file with help of the xp_cmdshell

In the above codes, we have used two different variables and one table variable. Now let’s explain what is their tasks.

In this second part of the query, we assigned the required values into the parameters. We set the MS-DOS command into the @MsDosCommand variable. The /B switch here allows only file names and extensions to return as a result of the “dir” command. If we run this command in the MS-DOS command line the result likes as below.

dir command in MS-DOS

In this part of the query, we executed the xp_cmdshell and populated the result into the table variable so that we will use this table to check the underlining file existence to use it.

In this last part of the code, if the file exists the code block returns File Exists result otherwise it returns File Not Exists.

Conclusion

In this article, we have learned how to verify a particular file exists in a specific folder or does not exist in SQL Server. We can use alternative methods to perform this task:

  • xp_fileexist extended procedure
  • sys.dm_os_file_exists dynamic management function
  • xp_cmdshell extended procedure

Esat Erkec
DBCC, SQL commands

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

402 Views