How to Copy Files in SQL Server? – Interview Question of the Week #257

Question: Can SQL Server copy a file? SQL Server 2019 and later include master.sys.xp_copy_file for one file and xp_copy_files for a matching set. Treat these as undocumented extended procedures, not a portable file-management API.

The small distinction is easy to miss. The multiple-file procedure has an extra “s.” The single-file form names the source and destination files; the multiple-file form uses a source pattern and a destination directory.

-- Inspect availability without copying anything.
SELECT name, type_desc
FROM master.sys.all_objects
WHERE name IN ('xp_copy_file', 'xp_copy_files');

-- Deliberate lab-only copy examples, not executed by this script:
-- EXEC master.sys.xp_copy_file
--     'D:\SQLAuthorityCopyLab_190683\source\one.txt',
--     'D:\SQLAuthorityCopyLab_190683\single\one.txt';
-- EXEC master.sys.xp_copy_files
--     'D:\SQLAuthorityCopyLab_190683\source\*.txt',
--     'D:\SQLAuthorityCopyLab_190683\multiple\';

The first query only checks whether those objects are present. The commented commands show the single and multiple forms against a dedicated lab folder. Source files and destination directories must already exist, and permissions belong to the account SQL Server uses for filesystem access, not automatically to the person running SSMS.

Because these procedures are undocumented, verify behavior on the exact build before relying on them in maintained automation. Presence in master alone doesn’t establish a support guarantee.

For an ordinary Windows file copy, PowerShell is easier to review:

Copy-Item -LiteralPath 'D:\CopyLab\source\one.txt' -Destination 'D:\CopyLab\single\one.txt'
Copy-Item -Path 'D:\CopyLab\source\*.txt' -Destination 'D:\CopyLab\multiple\'

Those commands copy files; they are not a way to make a consistent backup of attached MDF or LDF files. Use SQL Server backup and restore for a database.

xp_copy_file is not a backup tool, it is an undocumented file copy, so test it on your build first.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.