.bat file to deploy database from server

[John Gray]

Leesha

I've just got time before visiting my friendly vampire nurse to have a very brief look at your ZIP file, but the "network path cannot be found" is (probably!) because you missed changing the line
set source=\\%server%\rest-of-path\%dbname%
just before the COPY command (where I didn't know what the path-to-the-database was on the server). This assumes that's where your test file is located.
I also have to give you an XCOPY command instead of COPY...

More when I return.

[grovelli]

Hi John, can I ask where your command
set forcmds=findstr /i /b "::##" %~f0
goes looking for PCname and Username?
I mean, does it look for all the pc's and usernames that are networked within a company?

[John Gray]

That SET command simply sets an environmental variable %forcmds% which I plug into the FOR command which follows it.

If, in a Command Prompt window, you produce the help information for this and many of the other commands you ask about in your previous post, as SET /?, TITLE /?, and so on, you'll see how they work. The FOR command is by far the most powerful BATch file command, as you will see from FOR /?.

The FINDSTR command you mention looks in the BATch file itself (which can be referenced as %~f0, and I realise that it should probably be in double-quotes, in case there's a blank in the path) for the string ::## at the beginning of a line, and takes parameters 1 and 2 (the pcname and the user name) from where they have been supplied in the table at the beginning of the file.
The point of this is so that there is only a single place where modifications are made, and you don't have to have a special 'pcnames and usernames' file to remember to drag along with BATch file to a new directory, say.

I'll answer a bit more from your earlier post later this afternoon.

In passing, I would make the point that BATch files are less-frequently encountered nowadays, except from people who have been writing them all their lives (well, the last 25 years!). If i was starting from scratch, now, I would go for PowerShell - considerably more powerful, and where Microsoft's efforts are directed.

[John Gray]

Leesha

Here's the Xcopy insert - see what happens with it - it may need a /-y switch

Code: Select all

:: Replace the four following lines:
:: perform the database copy
set source=\\%server%\rest-of-path\%dbname%
set target=\\%pcname%\c$\RSM\Database
copy %source% %target%

:: with all the following...

:: back up the current database on this PC
set dbpath=\\%pcname%\c$\RSM\Database
set bkpath=%dbpath%\old
xcopy %dbpath%\%dbname% %bkpath%
if errorlevel 1 (
  echo %stamp%  backup of current database on %pcname%, %username%, FAILED >> %log%
  echo %stamp%    so no attempt made to copy database from server          >> %log%
  goto :eof
)
:: perform the database copy
set source=\\%server%\rest-of-path\%dbname%
set target=\\%pcname%\c$\RSM\Database
xcopy %source% %target%

Don't forget to change the rest-of-path!

[John Gray]

This is such a great thread
1) John's code packs a real punch but how does one know the meaning of all the commands? I've tried http://www.microsoft.com/resources/docu ... x?mfr=true" onclick="window.open(this.href);return false;
but I don't see all the commands John uses such as "Title" or "Stamp" or "findstr" or "start" or "set" and what the meaning of "%" is in as Set command or where the labels such as :process or :datetime the Call command is supposed to jump to are.
2) Is there any way to process each line in the batch file step by step like you can do in the VBA window so you see what's happening?

Some of your questions I have answered already, but here are some more answers:

1. "stamp" is a variable I set up in the :datetime subroutine to hold the value yyyymmdd_hrmn, such as 20110128_1320
2. all the other commands are build into the CMD interpreter, see <command> /? in a Command Prompt window to get help info.
3. the % character is used to indicate the contents of an environment variable. e.g. SET newvar=%oldvar% means set the environment variable newvar to have the same contents as has oldvar. Variables or text can be concatenated together simply by placing them nest to each other (as in my ECHO commands).
4. %%a (and so on) in FOR commands is a loop variable.
5. call :subroutine parm1 parm2 ... passes the indicated variables to the :process subroutine, where they appear as positional variable %1, %2, etc
6. "goto :eof" is an end-of subroutine, or, at the outermost level, an end-of-BATch-file, command
7. to determine what is going on in a BATch file, change the @echo off at the top to something else (I always use @echo .off, out of habit), which causes each executed statement to be written to the console, and insert PAUSE commands on new lines all through the BATch file to stop it so you can read what's gone on.

Hope that makes a little sense?

[HansV]

Hi John...

Giorgio,

The e-mail notification from the Lounge to you cannot be delivered. Has your e-mail address changed? If so, please update your profile. If not, contact your ISP - they may block Lounge e-mails as spam.

[grovelli]

Hans, you said earlier that notifications can be a bit erratic so I guess that's what's happening because up to now I was receiving them for every single message posted on this thread and they haven't ended up in the spam folder and my email address hasn't changed so that's a bit odd.
John, your insights are really helpful, thank you for your time. I need some time to digest what you wrote but meanwhile, since you mentioned PowerShell as the new wave from MS, what does PowerShell offer as a substitute for batch files?

[HansV]

PowerShell resembles higher-level programming languages such as JavaScript, with a more extensive syntax than batch scripting. See for example PowerShell Tutorial for Beginners (follow the links on the right hand of the page).

[grovelli]

Thanks Hans, so if Leesha had wanted to have gone the PowerShell way, would she have ended up with a script.ps1 or something similar(is it a text file or what?) to be run at the PowerShell command line?

By the way, the notifications have resurfaced in my mailbox :-P

[HansV]

I don't know anything about PowerShell, Giorgio.

Perhaps the bounced e-mail was just a temporary glitch...

x500.png

[Leesha]

Hi,
I retried the code today using "administrator" as the login name as all the computers have this on it but it didn't work either. Still giving the network path error. I also tried the new code you posted above. I'm attaching the code so you can see if I've inserted correctly etc.
Thanks so much for your help on this!!
Leesha

@echo off
title DBdeploy
echo +----------+
echo I DBdeploy I deploy Access database from server to several PCs
echo +----------+

setlocal
::--------------------------------
:: TABLE of PC names and users for the copy
:: must have "::##" prefix followed by space, PCname, space, username
::## IKINNEY administrator
::## GIBSON administrator
::## SUSIECHARTIER administrator
::--------------------------------

:: set up some variables
set server=HeadNurse
set dbname=test.mdb & :: of course this needs changing! test using a dummy file?

:: set up the current date and time variables yy, mm, dd, hr, mn, stamp, currtime
call :datetime

:: set up a date-and-start-time-stamped log in the current directory/folder
set log=%~dpn0_%stamp%.log

:: obtain each PCname/username in turn, and process it
:: look for string V--V in this BATch file
set forcmds=findstr /i /b "::##" %~f0
for /f "tokens=1-2 delims=:# " %%a in ('%forcmds%') do (
call :process %%a %%b
)
:: inspect the log, assuming we're watching the BATch file run
start "" notepad %log%

:: that's it, folks!

endlocal
goto :eof

::----------------------------------------------------------------------------
:process a single PCname and username
set pcname=%1
set username=%2

:: obtain current timestamp
call :datetime

if not defined pcname (
echo %stamp% ":process" called without any parameters
echo %stamp% ":process" called without any parameters >> %log%
goto :eof
)

:: console message, in case anyone is watching
echo %stamp% processing: %pcname% %username% ...

:: test whether this PC is active by PINGing it
:: look for "TTL=" as positive response
ping -n 1 %pcname% | findstr "TTL=" > NUL
if %errorlevel% gtr 0 (
echo %stamp% %pcname% used by %username% is not active >> %log%
goto :eof
)

:: obtain timestamp for start of copy
call :datetime
set start=%currtime%

:: back up the current database on this PC
set dbpath=\\%pcname%\c$\RSM\Database
set bkpath=%dbpath%\old
xcopy %dbpath%\%dbname% %bkpath%
if errorlevel 1 (
echo %stamp% backup of current database on %pcname%, %username%, FAILED >> %log%
echo %stamp% so no attempt made to copy database from server >> %log%
goto :eof
)
:: perform the database copy
set source=\\%server%\rest-of-path\%dbname%
set target=\\%pcname%\c$\RSM\Database
xcopy %source% %target%

:: save the errorlevel from copy
set el=%errorlevel%
call :datetime
set end=%currtime%
if %el% equ 0 (
echo %stamp% database copy for %pcname%, %username%, took from %start% to %end% >> %log%
) else (
echo %stamp% database copy for %pcname%, %username%, FAILED >> %log%
)
:: end of :process subroutine
goto :eof

::=============================================================================
:datetime
:: generalised DATE and TIME routine for all regions
:: %yy% will contain four digits, 20nn;
:: %mm%, %dd% and time variables %hr%, %mn%, %sc% and %th% contain two digits
:: %stamp% will be formed as %yy%%mm%%dd%_%hr%%mn%
if "%date%A" LSS "A" (set toks=1-3) else (set toks=2-4)
for /f "skip=1 tokens=2-4 delims=(-)" %%a in ('echo.^|date') do (
for /f "tokens=%toks% delims=.-/ " %%e in ('date /t') do (
for /f "tokens=5-8 delims=:., " %%i in ('echo.^|time') do (
(set %%a=%%e) & (set %%b=%%f) & (set %%c=%%g) & rem create yy, mm, dd
(set hr=%%i) & (set mn=%%j) & (set sc=%%k) & (set th=%%l)
rem note the variable names for Time: hr, mn, sc, th
)))
if 1%yy% LSS 200 (set yy=20%yy%) & :: ensure 4-digit years in %yy%
if 1%hr% LSS 20 (set hr=0%hr%) & :: ensure 2-digit hours in %hr%
:: timestamp variable yyyymmdd_hrmn
set stamp=%yy%%mm%%dd%_%hr%%mn%
:: current time variable hr:mn:sc
set currtime=%hr%:%mn%:%sc%
goto :eof
::=============================================================================

[grovelli]

Hi, I'm not an authority on this by any stretch of the imagination so I might be barking up the wrong tree but have you tried using the name you get by right-clicking the My Computer icon on the pc desktop and selecting the Computer Name tab? I suppose that's the tag the pc is idenfied with on the network that includes the server you want to place your file updates on.

[grovelli]

You forgot to substitute your path in
set source=\\%server%\rest-of-path\%dbname%

[John Gray]

You forgot to substitute your path in
set source=\\%server%\rest-of-path\%dbname%

Spot on!

[grovelli]

Thanks John, am I spot on about the computer names too? And what does PowerShell offer as a substitute for batch files?

[Leesha]

Hi John,
I finally had a chance to get back to this. Getting closer. I replaced the copy code with the xcopy code and updated the server path name. Now the error that comes up in the cmd prompt is that the file can't be found. The files is called test.mdb and is in C:\autoload\test.mdb.

I'm uploading the error file and txt file.

Thanks!
Leesha

[grovelli]

set source=\\%headnurse%\C$\Autoload\%dbname%
Your server is your pc's C: disk?

[Leesha]

No, I just assumed I needed to put whole root in. I tool out the C$\ Two of the computers are not turned on so they didn't through. I verified this by trying to ping them with no luck. The third computer is on (I could ping it) but the transfer failed. I wasn't able to see the cmd message to see what is going on with it.

Leesha

[John Gray]

Hi Leesha

The problem is the path again! You have

set source=\\%headnurse%\C$\Autoload\%dbname%

but the %headnurse% variable does not (and should not!) exist.
The statement should be:

set source=\\%server%\C$\Autoload\%dbname%

[grovelli]

But then, now it's me who doesn't understand
Aren't you mixing references to a networked server and the local C: drive in
set source=\\%server%\C$\Autoload\%dbname%
?