Post reply

xp_cmdshell permission issue

  • December 14, 2015 at 9:24 am

    #303287

    I have a large database migration to perform (about 3000 databases) and we don't really want downtime for the duration of the entire move...

    so we decided to move one database at a time

    we only need to move the log files for the databases, so the idea was to do the following steps

    1)identify a migration candidate and kill connections

    2)detach

    3)move logs

    4)re-attach

    so we built a dynamic script that creates the following code for a single database

    Kill 684

    exec sp_detach_db @dbname='xxxx'

    exec xp_cmdshell 'copy S:\Log\xxxx_log.LDF v:\logs\xxxx_log.LDF'

    exec sp_attach_db @dbname='xxxx',@filename1='V:\Data\xxxx.mdf',@filename2='v:\logs\xxxx_log.LDF'

    my problem is the xp_cmdshell command fails....

    s:\ and all subfolders have "anyone full control"

    v:\ and all subfolders have "anyone full control"

    if I open CMD and run the 'copy S:\Log\xxxx_log.LDF v:\logs\xxxx_log.LDF' it works fine

    but through sql management studio I get "access is denied, 0 files copied"

    SQL can read the data in both the source and target folders .. it can also run an xp_cmdshell 'dir v:\' etc on both drives....

    any ideas what i'm missing????

    MVDBA

  • December 14, 2015 at 9:31 am

    #1845853

    Shouldn't the copy syntax be like this?

    copy S:\Log\xxxx_log.LDF v:\logs

    John

  • December 14, 2015 at 9:54 am

    #1845860

    we're standardising names as we go. but as I said, the script works fine if I just run CMD and copy the contents into the window.. the issue is "permission denied" when run via SQL

    MVDBA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply