Need help using MoveDir with a varible

[owensct]

Hi All,

I've got a head scratcher that I am hoping someone could help me with as I'm trying to finsih up some code for our HR department that I need to put into production by Monday morning.

I have written a Autoit script that shuts down a database engine and then does a copy of the database to a folder on the database server. Unfortunately the backup space on the server is limited so I have been manually moving the backups to another server twice a week until I could find the time to automate the move of the files and folders as part of the script. Well the time to automate it is this weekend per my CIO.

Because the end users may do multiple backups in a given day, I have assigned the month-day-year_hours-minutes-seconds to a variable and I rename the initial backup folder and logfile by appending the datetime variable to them. So the folder Daily_ becomes Daily_06302012_192515 and Daily_.log becomes Daily_06302012_192515.log. Using the FileMove function I have been able to get it to move the log file but for the life of me I can't get it to move the folder . So I tried to use DirMove, but I can't get it to work either. The process runs, but the folder in still on the original server when it finishes. The code I am using to try and accomplish this is as follows:

$datetime = @MON & @MDAY & @YEAR & "_" & @hour & @min & @SEC

RunWait('PsExec \\RHP-VMS-001 robocopy "\\RHP-VMS-001\ANSOSONESTAFF\DATA" "\\Rhp-vms-001 Staff_backups\OS_Daily\daily_" /COPYALL /S /E /V /LOG+:"

RunWait(@comspec & " /c ren \\rhp-vms-001\staff_backups\OS_Daily\daily_ Daily_" & $datetime)

RunWait(@comspec & " /c ren \\Rhp-vms-001\staff_backups\OS_Daily\Daily_.log Daily_" & $datetime & ".log")

RunWait('sc \\RHP-VMS-001 start "Pervasive.SQL (relational)"', '', @SW_HIDE, 2)

RunWait('sc \\RHP-VMS-001 start "Pervasive.SQL (transactional)"', '', @SW_HIDE, 2)

FileMove("\\rhp-vms-001\staff_backups\OS_Daily\*.*", "\\rhp-diskbackup\onestaff\OSTest_Daily")

DirMove("\\rhp-vms-001\staff_backups\OS_Daily\Daily_" & $datetime, "\\rhp-diskbackup\onestaff\OSTest_Daily")

Else

MsgBox(0,"One Staff Backup", "Database engine is down please contact the IS Department")

Exit

etc

etc

Any help is greatly appreciated as I can;t seem to fignure out what I'm doing wrong.

Thanks

Gordon

[John]

It appears it has to do with being a UNC path which makes DirMove work differently, making it a copy/delete operation instead. From the help file:

If the destination already exists and the overwrite flag is specified then the source directory will be moved inside the destination.

It seems this is the behavior you expect so you would need to set the overwrite flag. Set the overwrite flag to 1.

Just tested across drives on my machine. Same thing. Overwrite flag must be set to 1 to work as expected.

[John]

In case you are wondering, the entire contents of the folder will not be overwritten with the overwrite flag set to 1. It will simply merge them and overwrite any files by the same name.

[owensct]

Thanks for the suggestions, unfortunately it didn’t work. It was copying the data folder, but never copied more than 4 files, 30K of 1.32GB. Further digging and I found out that the script is crashing with the dreaded “this program has stopped responding” error.

I then tried moving the script to the actual server, installing AutoIt on the server, removing all the UNC references and replacing them with the literal and RDP’ing into the server and running it from there, same thing, it keeps hanging. So I ran it again and left it for an hour and it never made it past 3 files copied. I actually had to kill AutoIt to kill the script.

I then tried executing a robocopy command directly from a command window on the server (robocopy e:\ANSOSONESTAFF\DATA Z:\OSTest_Daily\daily_ /E /FFT /Z /W:5 /LOG:z:\OStest_Daily\Daily_.log) and it took over 27 minutes to copy 1.32 GB, 1308 files and 3 folders with a transfer speed of 58.741 MB/minutes, now all our server to server connections are at minimum gigabit and many are fiber, so something is definitely wrong. I tried the same transfer on my home network, using the exact same command, and my network which isn’t fast by any means only took 11 minutes to move 5.49 GB, 6208 files in 478 folders. Yes I know it’s not apples to apples, but an enterprise network that takes 27 minutes to move 1.32 GB? Something is not right here. Maybe the network manager throttled down my bandwidth since I pissed him off Friday by making him change rights for user access late Friday that he was supposed to have done weeks ago, lol.

Oh and one more thing I tried using xcopy instead of robocopy and it was marginally faster, but still not good enough for me to consider releasing to my end user community. Also, it wasn’t network traffic as there is hardly anybody on the system on a Sunday afternoon except maybe a few die hard accountants and me .

Anyway the changes have been made, but I can’t put this into production, 27 minutes to backup the database? My users would lynch me, have me drawn and quartered and burn what was left for good measure.

Anybody got any other suggestions? Other than reading our network manager the riot act and asking him to explain how it is that a low end home network can transfer nearly 4 times the data is less than half the time as a million+ enterprise class environment.

Another weekend, blown to hell sitting at my desk working when I could have been at the beach.

Thanks

Gordon

[John]

I'm not following the code you provided very well. You have and "Else" with no "If" or "Endif". Your description says you are shutting down a database, but that's not what I'm seeing in the command. The "Else" also seems to expect the database to be running, even though a lot of code is apparently missing.

If the database is to be shut down then I would check to see if it actually is. Looking at:

http://msdn.microsoft.com/en-us/library/ms188767.aspx

The database will not simply shut down immediately unless the WITHNOWAIT option is set. Otherwise it goes through a number of steps first. A database that is copied while still in use could be a big problem. If you tested on the actual server bandwidth throttling by the admin should be an issue.

One more thing, on the link I provided it says:

SHUTDOWN permissions are assigned to members of the sysadmin and serveradmin fixed server roles, and they are not transferable.

Underline added. If the rights changes you had the admin do were intended to give rights to the shutdown command it likely failed. Everybody would effectively have to be a full fledged sysadmin.

[owensct]

John,

Here is a more complete copy of the code. I was only showing the relevant portion for the sake of brevity. The database is shutdown, this is handled earlier in the script. I have also shutdown the database manually just to be sure for testing. Understand this script has been working fine for months, it’s just that I now need to modify it so that it backs up the database files to the backup server instead of on the local server which is what it has been doing up until now.

In the code below I have commented out the FileMove and DirMove which is what I was originally trying to do. I then installed AutoIt on the server in question and modified the script so that it no longer uses UNC, but the literal, \\rhp-diskbackup\onestaff has a drive mapping on the server that contains the database and the location of the database files on server is e:\ansosonestaff\data.

As I mentioned previously when I run it this way the script dies and if I run the Robocopy command in a command window on the server it take bloody forever for some reason, (I've got my network manager looking into it). I suppose I could perform the backup locally on the server, restart the database engine and then bring up a message box that lets the user know that the database is back up and available and that files are being moved to the backup storage system and once complete popup another message box telling the user that the process is complete and that they can safely exit the application. It just seems like an awful convoluted approach to do something that seems like should be fairly straight forward.

Also, I did not mention this in my previous posts and I’m not sure that it would matter, but there is an AD group called OneStaff and all users that have access to this database that are part of and that group has full control on rhp-diskbackup and the local server directories where the database files reside. I am a member of that group and a domain admin on our network. So in theory everyone who is a member of that group should have no issues and as a domain admin I certainly shouldn’t be having any issues relating to rights. Further, in talking to our network admin, he told me that the rhp-diskbackup is a CIFS share which is running on our IBM NAS which runs a variant of Linux.

One more thing I am going to try to see if it makes a difference is to try and run the script from one Windows server to another Windows server in the unlikely event that the problem lies in how the CIFS share is configured.

Thanks for all the help with this one.

Gordon

expandcollapse popup

; Run the GUI until the dialog is closed
While 1
Switch GUIGetMsg()
Case $GUI_EVENT_CLOSE
     ExitLoop
Case $BtnQ1 ; Calls Logged on user program from One Staff server
GUISetState(@SW_HIDE) ;Hides GUI when USERLOG.EXE runs
; Run program USERLOG.EXE from Specified One Staff server to verify that users are all off the system
RunWait("e:\ANSOSONESTAFF\USERLOG.EXE") ; Command makes the script wait until user is finished with USERLOG.EXE
GUISetState(@SW_SHOW) ; Restores GUI AFter run of USERLOG.EXE is complete
Case $BtnQ2 ; Starts daily backup process
; Asks if user have verified that all staff are logged off system
$answer = MsgBox(4, "Daily Backup", "Have you verified all users are signed off One Staff?")
If $answer = 7 then
     MsgBox(0," Daily Backup", "Please ensure all users are logged off One Staff before continuing")
Else
     $pid = Run('sc \\RHP-VMS-001 query "Pervasive.SQL (relational)"', '', @SW_HIDE, 2)
     Do
     $data &= StdOutRead($pid)
     Until @error
     If StringInStr($Data, 'running') then
     RunWait('sc \\RHP-VMS-001 stop "Pervasive.SQL (relational)"', '', @SW_HIDE, 2)
     RunWait('sc \\RHP-VMS-001 stop "Pervasive.SQL (transactional)"', '', @SW_HIDE, 2)
     ;001-GBO
     ; 002-GBO
     $datetime = @MON & @MDAY & @YEAR & "_" & @hour & @min & @SEC
     RunWait(@ComSpec & " /c xcopy e:\ANSOSONESTAFF\DATA" "e:\Staff_backups\OS_Daily\daily_" /S /E /V /LOG+:"e:\Staff_backups\OS_Daily\Daily_.log"')
     RunWait(@comspec & " /c ren e:\staff_backups\OS_Daily\daily_ Daily_" & $datetime)
     RunWait(@comspec & " /c ren e:\staff_backups\OS_Daily\Daily_.log Daily_" & $datetime & ".log")
     RunWait('sc \\RHP-VMS-001 start "Pervasive.SQL (relational)"', '', @SW_HIDE, 2)
     RunWait('sc \\RHP-VMS-001 start "Pervasive.SQL (transactional)"', '', @SW_HIDE, 2)
     ; FileMove("e:\staff_backups\OS_Daily\daily_" & $datetime & ".log", "z:\OSTest_Daily\Daily_" & $datetime & ".log")
     ; DirMove("e:\staff_backups\OS_Daily\Daily_" & $datetime, "z:\OSTest_Daily")
     Else
     MsgBox(0,"One Staff Backup", "Database engine is down please contact the IS Department")
     Exit
     EndIf
EndIf

[John]

There are too many potential variables outside AutoIt that I can only guess at. The issue with Robocopy/xcopy does seem to indicate issues outside the script need addressed, and I'm not in the position to be much help there. That smells to me like the file being backed up is still in use by something. I can only provide wild guesses for you to consider.

In the script you appear to be asking the user to verify all users are signed off. In order to know this they must issue an sp_who command.

http://msdn.microsoft.com/en-us/library/ms174313.aspx

It would be better if the script retrieved this information and acted accordingly. Users are never consistent. The 'VIEW SERVER STATE' permission must be set or it'll merely return the current session.

Based on the xcopy issue make sure that the the backup being copied is not in use, by the database or any other process.

The CIFS share is highly suspect in this case, since it uses SMB to allow file changes to occur simultaneously on both the server and client. This makes CIFS effectively another logged on user keeping the backup in use irrespective of the database service state.

Other than that I can't help much without playing where I have no business being.