December 14, 2015 at 9:24 am
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
Shouldn't the copy syntax be like this?
copy S:\Log\xxxx_log.LDF v:\logs
John
December 14, 2015 at 9:54 am
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