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

USE    master ;   
    GO  
    EXEC  sp _configure 
      'show advanced option' , 
      '1' ;   
   RECONFIGURE WITH  OVERRIDE ;

• 
• Enable Xp_cmdshell extended stored procedure

EXEC  sp _configure    'xp_cmdshell' ,   1 ;   
    GO  
    RECONFIGURE ;

• 
• 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.

  EXEC    xp_cmdshell 
   'copy C:\NPE C:\backups' ;

• 
• 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.

EXEC  xp _cmdshell    'del C:\NPE\script.sql'

• 

#sql commands #sql server 2019 #t-sql #sql