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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy