File Copy and Delete Using TSQL

Today I want to copy a file on a physical drive of my server to another drive and then delete the original file. Here are some options that I have:

Using SQL 2019+

-- This copies a single file
EXEC master.sys.xp_copy_file 
'C:\MyFolder\test.txt', 
'D:\MyOtherFolder\test.txt';

-- This copies multiple files
EXEC master.sys.xp_copy_files
'C:\MyFolder\*.txt', 
'D:\MyOtherFolder\';

-- This deletes the single file
EXEC master.sys.xp_delete_files
'D:\MyOtherFolder\test.txt';

-- THis deletes multiple files
EXEC master.sys.xp_delete_files
'D:\MyOtherFolder\*';

Prior to SQL 2019

USE master;  
EXEC sp_configure 'show advanced option', '1';  
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE;

-- Copy Single FIle
DECLARE @cmdSql VARCHAR(1000)
SELECT @cmdSql = 'copy C:\MyFolder\test.txt D:\MyOtherFolder\test.txt'
exec master..xp_cmdshell @cmdSql

-- Copy multiple files
EXEC xp_cmdshell 'copy C:\MyFolder D:\MyOtherFolder';


--Delete single file
EXEC xp_cmdshell 'del D:\MyOtherFolder\test.txt'

--Delete multiple files
EXEC xp_cmdshell 'del D:\MyOtherFolder\*.txt'

File Directory Extraction

This code will give a list of files in a directory.

DECLARE @PathRoot varchar(250) ='D:\MyFolder\'
EXEC master.sys.xp_dirtree @PathRoot, 1,1;

DECLARE @output TABLE (output NVARCHAR(MAX))
DECLARE @cmd VARCHAR(8000) = 'dir D:\MyFolder\'

INSERT INTO @output
EXEC XP_CMDSHELL @cmd

SELECT   CAST(LEFT(output,20) AS DATETIME) AS modifiedDateTime,
          CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN 'Directory'
		      ELSE 'File' 
		  END AS type,
		  CAST(CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN NULL
		       ELSE REPLACE(SUBSTRING(output,25,15),',','')
		  END AS BIGINT) AS fileSize,
		  LTRIM(CASE WHEN SUBSTRING(output,25,5) = '<DIR>' THEN SUBSTRING(output,30,999)
		       ELSE SUBSTRING(output,40,999)
		  END) AS name
  FROM @output
 WHERE output IS NOT NULL
   AND LEFT(output,1) <> ' '
   AND output NOT LIKE '%<DIR>%.'
 ORDER BY type, name

AWS RDS SQL Server File Copy & Delete

In AWS RDS you must have S3 Integration enabled and permissions set up first. This will involve configuring access on the RDS USER ROLE and the BUCKET POLICY.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "ModifyBucketPolicy",
            "Action": [
                "s3:GetBucketPolicy",
                "s3:PutBucketPolicy"
            ],
            "Effect": "Allow",
            "Resource": "arn:aws:s3:::DOC-EXAMPLE-BUCKET"
        },
        {
            "Sid": "AccessS3Console",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListAllMyBuckets"
            ],
            "Effect": "Allow",
            "Resource": "arn:aws:s3:::*"
        }
    ]
}

{
	"Version": "2012-10-17",
	"Id": "Policy1609944108868",
	"Statement": [
		{
			"Sid": "Stmt1609944086534",
			"Effect": "Allow",
			"Principal": {
				"AWS": [
				    "arn:aws:iam::1234456789999:role/mycompany-database-rds-role",
				    "arn:aws:iam::1234456789999:role/aws-service-role/rds.amazonaws.com/AWSServiceRoleForRDS"
				    ]
			},
			"Action": [
				"s3:GetBucketACL",
				"s3:GetBucketLocation",
				"s3:GetBucketPolicy",
				"s3:ListBucket",
				"s3:PutBucketPolicy"
			],
			"Resource": "arn:aws:s3:::mycompany-database-bucket"
		}
	]
}

Now that I have configured my AWS RDS SQL Server, here is the code I’ll use to upload, copy and delete files between the Physical D: drive on RDS and my S3 bucket.

-- DOWNLOAD FROM S3

exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::bucket_name/test.txt', @rds_file_path='D:\S3\test.txt', @overwrite_file=1; 

-- UPLOAD TO S3
exec msdb.dbo.rds_upload_to_s3 @rds_file_path='D:\S3\test.txt', @s3_arn_of_file='arn:aws:s3:::bucket_name/test.txt', @overwrite_file=1;

-- DELETE FROM RDS S3
exec msdb.dbo.rds_delete_from_filesystem @rds_file_path='D:\S3\test.txt';

-- VIEW DIRECTORY ON RDS D:\S3\
EXEC msdb.dbo.rds_gather_file_details; -- Returns INT X

SELECT * FROM msdb.dbo.rds_fn_list_file_details({REPLACE WITH INT X});