detach and move .mdf,.ldf script
-
I posted here: http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=15584 but am still not sure I'm on the right track. I ran my generated script on a single db to test, and noticed that the .ldf and .mdf files on the server taking over were the old files, which means the new versions are not copying over correctly. The database was attached, but only by using the files that already existed in the directory my script uses. My sql to copy the files over is like:
USE master EXEC xp_cmdshell 'copy \\server1...\ASPState_log.LDF \\server2...\data2', NO_OUTPUT
which is used in the following script:
ALTER DATABASE ASPState SET offline WITH ROLLBACK IMMEDIATE
GO
sp_detach_db 'ASPState'
USE master EXEC xp_cmdshell 'copy \\server1\....\ASPState.mdf server2...\MSSQL\data2', NO_OUTPUT
GO
USE master EXEC xp_cmdshell 'copy \\server1...\ASPState_log.LDF \\server2...\data2', NO_OUTPUT
GO
I am querying msdb.backupfile and msdb.backupset to make my script. Can somebody tell me why my files aren't copying?
-
Have you tried running the copy commands at the dos prompt?
The only other thing I can think may be an issue is the Proxy account and ntfs security.
"Keep Your Stick On the Ice" ..Red Green -
I will use xcopy instead of copy command.
try the following:
exec xp_cmdshell 'xcopy "\\server1...\aspstate.mdf" "\\server2..\data2\aspstate.mdf" /s /e /y'
Also, I will make sure to map the resource directory in the distination server.
I hope this will help you solve the problem
Mamdoh
"And life is what we make it, always has been, always will be"
-
The suggestions above are great ones. The attach merely picks up the files that are there. If you got one file and not the other, it would error out because they wouldn't be insynch. So the problem is your copy. Be sure that works, try it from DOS, try it from QA and be sure you can copy files and then you will know what is or isn't working.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Thanks for your help already guys - this really helps! Here is my updated script:
USE master EXEC xp_cmdshell 'xcopy "\\server1\....\ASPState.mdf" "\\server2...\MSSQL\data2" /y'
I decided to only use the y switch because the other 2 pertained to folders and subfolders (I'm copying 1 file at a time here). This command does work in DOS on my machine and the machine running SQL Server Agent that tries to run this command. However, in QA, my return values are:
Invalid drive specification
0 File(s) copied
NULL
Any thoughts??
-
Try to map the resource folder in the distination server which is the one will run the script and give it a letter like H then use the script as follow
USE master EXEC xp_cmdshell 'xcopy "H:\ASPState.mdf" "c
rogram Files\Microsoft SQL Server\MSSQL\Data" /y'Mamdoh
-
That doesn't seem to be working for me either - I know I could just use c# and move the files that way, but I'd rather have this wrapped up as a script. I guess that would be a failsafe way to do it though...
-
What's the Windows account under which your SQL Server is running? Does it have permissions to see your source and destination servers and folders?
-
good points everyone. Try looking at your proxy account. Also check to see who you are running the xp_cmdshell command under.
I guess the point is.... What account is being used when xp_cmdshell hits the operating system. This is where your Proxy account may come into play. Is it set? Is xp_cmdshell for admin users only? etc.
"Keep Your Stick On the Ice" ..Red Green -
I see that the account running SQL Server Agent (<myDomain>\ClusterAccount) has full control on the folder I wish to copy from, and the Everyone group has full control on the folder I wish to copy to. bbychkov and Jeff W, does this answer your question?
For future reference, the server I wish to use as a backup (not in my cluster) does not have a proxy account set (EM > Management | Sql Server Agent | Properties the "Service Startup Account" is set to system account). Who will this run as for instances where I wish to copy and attach back to the cluster? I imagine I'll have to add whatever user this is to have write capabilities on the cluster folder when the time comes to copy back to it. At present, the security tab for the cluster folder shows only 2 users: <myDomain>\ClusterAccount and the server_name\Administrators (server, not domain) group.
Many thanks again.
-
Brett,
First of all, let's try to execute the command successfully from Query Analyzer.
This is from BOL:
"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail."
Assuming that you connect to the SQL Server from Query Analyzer as sa the security context will be for account under which SQL Server Service (not SQL Agent!) is running.
If you're not using the sa (or another sysadmin) then verify the permissions for proxy account on the server to which you're connected.
-
in your previous message you mentioned that when you run xcopy statement from command prompt window the statement executed successfully. I am not sure what causing this statement to run under the command prompt and not from xp_cmdshell.
if that the case the try one of these two solution:
::Solution One=====
create a batch file like this
//////////Start batch file////////
@echo off
rem COPYFile.BAT transfers all files in all subdirectories of
rem the source drive or directory (%1) to the destination and over write existing one
rem drive or directory (%2)
xcopy %1 %2 /y
if errorlevel 4 goto lowmemory
if errorlevel 2 goto abort
if errorlevel 0 goto exit
:lowmemory
echo Insufficient memory to copy files or
echo invalid drive or command-line syntax.
goto exit
:abort
echo You pressed CTRL+C to end the copy operation.
goto exit
:exit
///////////End batch file////////////////
then add a job step (after the step to stop the SQL server) to your SQL server to exec the batch file
::Solution Two===
create a batch file like this
////////////////Start Batch File/////////////
net stop sqlserveragent
net stop mssqlserver
xcopy H:\ResourceDrive\Data\*.* C:\Progra~1\Micros~1\MSSQL\date /y
net start mssqlserver
net start sqlserveragent
goto abort
:abort
echo press any key to end the process
goto exit
:exit
////////////////////////End Batch File//////////////
then go to Start/accessories/systems Tools and select schedule task to run the batch above
Hope one of these solution will be a workaround for you
Mamdoh
-
I am using the sa user to connect to QA. When I run
EXEC master.dbo.xp_sqlagent_proxy_account N'GET'
(from BOL) I get no rows returned. I get the same thing in Ent. Mgr.
On the cluster server, I checked the Services under Administrative and see the service is running under the (since apparently no proxy is being used) <myDomain>\ClusterAccount account, which does have full control permissions on folders on both servers. Would it be running under MY account for some reason if I'm at my computer trying to execute the script???
I can just use C# to copy the files, but I will definitely consider this option if needbe!
Thanks.
-
Brett,
Can you, please, try to run your xp_cmdshell '...' from QA again and post the exact error message?
I also sent you a private message with more questions.
-
The information I get is in the 'output' column / field, and contains 3 rows for each xp_cmdshell command. They are the same for both:
Invalid drive specification
0 File(s) copied
NULL
I'll respond to the private message tonight (I don't have access to my home email from here).
Thanks
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply