0

on SQL we are creating a new folder by copying and existing folder by xp_cmdshell script.

SQL Script:

declare @cmd   sysname 
set @cmd = 'xcopy /t /e /i "C:\Rudresh\Process\001" "C:\Rudresh\Process\002" /O /X /H /K'                
exec master..xp_cmdshell @cmd

Script will throw Access Denied error upon execution and I have provided all the folder permission. kindly let me know is there any other permission i need to grant for SQL User.? or do i missed something configure (NTFS or ACL permissions)

Note: Script works fine if i remove /O /X from the script and creates the directory.

enter image description here

Thom A
  • 88,727
  • 11
  • 45
  • 75
Rudresh Kundur
  • 43
  • 1
  • 10
  • Please refer to [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15) `The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.` Verify that the service account has the necessary required access rights – Squirrel Mar 22 '22 at 11:24
  • why have to added the powershell tag when the entire thing seems to be running in a CMD shell? – Lee_Dailey Mar 22 '22 at 11:33
  • 1
    This shouldn't even be a task you're doing in SQL Server. T-SQL is a scripting, programming or "do what ever you need" language. If you want to copy files using SQL Server, then use agent to run the powershell task in a Powershell job. Or, better, yet do the whole process outside of SQL Server using an application designed for the actual task. – Thom A Mar 22 '22 at 11:44
  • The better question is why you are using SSMS and a tsql query to do ad-hoc file system manipulation. And sysname is the wrong datatype to use for dynamic sql generation - a potential problem for someone to fix in the future. – SMor Mar 22 '22 at 11:52

1 Answers1

0

You cannot safely do a plain copy of SQL Server database files when that database is open and in use by SQL Server. For this reason, these files are typically opened by SQL Server disallowing any sharing, even shared reads. There is an access privilege that can bypass even this, but I do not recommend it as the copied files will likely be corrupt and unusable.

Many (most?) BACKUP tools and products can safely copy these files, but this is because they use a special SQL Server interface and/or interlock to insure that they do not get files that are corrupted by SQL Server changing the contents midway through a copy.

So the solutions to this problem are either:

  1. Shutdown SQL Server (or detach the specific database) before you run your XCopy (then re-Attach the database when done), or
  2. Use a Backup tool that supports SQL Server instead, or
  3. Use SQL Server BACKUP commands to make backups of the databases and log files and then have your XCopy copy only the backup files, not the live files, or
  4. Just have SQL Server BACKUP do all of the work without any XCopy. SQL Server has a lot of facilities to support this approach and it is was most DBAs do for this kind of thing (these facilities using SQL Agent to run a Powershell job calling SQL Backup).
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137