Rajendra Gupta
Copy file using the xp_cmdhell

T-SQL scripts to copy or remove files from a directory in SQL Server 2019

August 21, 2020 by

Sometimes database professionals need to perform specific tasks at the operating system level. These tasks can be like copying, moving, deleting files and folders. A use case of these tasks might be removing the old backup files or copying backup files to a specific directory after a particular time. In SQL Server, we can use xp_cmdshell extended stored procedure to execute commands directly in the Windows command prompt(CMD). You need a sysadmin role or proxy account configured to use this extended procedure. We can also use the SSIS package for the file transfer, but it also requires you to build a package with the relevant tasks.

SQL Server 2019 introduced many new stored procedures, functions, dynamic management views(DMV). In this article, we will explore the xp_cmdshell procedure along with the new functions in SQL Server 2019 to copy or remove the files.

  • Environment details: You should have SQL Server 2019 instance. You can download the latest version from the Microsoft URL

A quick overview of the xp_cmdshell

This extended stored procedure is available in the master database. You need to enable it in the system configuration using the sp_configure.

  • Enable advanced options in the master database of the SQL Server 2019 instance

    Enable advanced options

  • Enable Xp_cmdshell extended stored procedure

    Enable Xp_cmdshell

    • Suppose we require copying files from one folder to another. We can use the COPY command in the XP_CmdShell for this purpose. In the below query, we copy all files from the source (C:\NPE) to the destination (C:\backups) directory.

    In the query output, you get a list of all copied files using the xp_cmdshell stored procedure.

    Copy file using the xp_cmdhell

    Similarly, we can use the DEL command in the XP_Cmdshell to remove a file from the specified directory. The below script removes a file script.sql from the C:\NPE directory.

    Delete a file

    SQL Server 2019 new stored procedures

    SQL Server 2019 introduces new stored procedures to copy and delete files using the T-SQL. With the introduction of these stored procedures, we do not rely on the xp_cmdshell that uses a Windows command shell for command executions.

    It provides below new stored procedures to copy and delete files using T-SQL:

    • sys.xp_copy_file
    • sys.xp_copy_files
    • sys.xp_delete_files

    Let’s explore these stored procedures in SQL Server 2019.

    Copy a single file using the sys.xp_copy_file stored procedure

    To copy a single file from the source to the destination folder, we use sys.xp_copy_file stored procedure. Specify the source and destination path in the query argument.

    The below script copies the [script.sql] from the c:\script.sql to C:\NPE.

    Copy a single file using sys.xp_copy_file stored procedure

    To explore the stored procedure further, create a notepad file in the source directory and write some text in it.

    Create a new file

    Run the below T-SQL and it copies the notepad file into the C:\NPE folder.

    Once the copy is finished, modify the source file and rerun the above T-SQL. We already have a file present in the destination directory, but the SQL query executes successfully. You do not get any error message. It shows that xp_copy_file overwrites the file and does not give any warning or information.

    You can see the updated file in the destination folder.

    Modify the source file

    • Note: You should be careful in using this stored procedure as it replaces the existing file without any warnings. You can embed a logic to check the file existence before copying it

    Copy multiple files using the sys.xp_copy_files stored procedure

    SQL Server 2019 provides a new stored procedure sys.xp_copy_files. You can use it to copy all files from the source to the destination folder. You can copy files with a specific pattern as well.

    To demonstrate this command, I have removed all files from the destination folder manually.

    The below T-SQL copy all files from the source (C:\NPE) to the destination (C:\backups) directory.

    • Source folder:

      Copy multiple files using sys.xp_copy_files stored procedure

    • Destination folder:

      Destination folder

    Suppose, we want to copy the files with a specific extension using this stored procedure. We can use an asterisk (*) wildcard to copy the files satisfying our requirements.

    • Source folder:

      Source folder

    • Destination folder

      View copied files

    In the destination folder, we verify that it copied SQL extension files in the NPE folder.

    Similarly, we copy the files whose name starts with the character C from the source folder.

    • Source folder:

      View source folder

    • Destination folder

      Copy files whose name starts with C

    Delete files using the sys.xp_delete_files stored procedure in SQL Server 2019

    SQL Server 2019 introduces new stored procedure sys.xp_delete_files to delete files from the specified folder.

    Delete a single file using the xp_delete_files

    For copying files, SQL Server 2019 gives two stored procedures xp_copy_file and xp_copy_files. For the delete operation, it has a single stored procedure xp_delete_files.

    To delete a single file, we specify the file name and path in the below T-SQL. It removes the CleanupBuildVersion_RunAfterEveryBuild.sql file from the C:\NPE folder.

168 Views