Sam Pointer

When I’m on a Windows box and I want to sync two directories (remote or local), I use this little one-liner:

@echo off
xcopy %1 %2 /M /E /Y /Z

The switches are:

• M: Copy only files with the archive bit set
• E: Copy directories and subdirectories, even empty ones
• Y: Supress prompting for overwrites
• Z: Copy in network restartable mode

The %1 and %2 obviously denote parameter arguments when called from a batch file, so when saved as ‘file_sync.bat’:

C:\>file_sync.bat c:\somedirectory \\someserver\sometargetdir

Will sync the local directory with the remote share. When files are modified or added, Windows sets the NTFS archive bit. When run again the script will only copy the new or amended files.

Assuming that %1 is set to a valid path and %2 the name of a database, and the script is being run from the database server itself:

echo Testing %1 to ensure that it is a directory...
if exist %1\NUL (echo It is.) else goto forced_exit
for %%i in (%1\*.sql) do sqlcmd -E -d %2 -m-1 -p 1 -o %%i.log -i %%i
if /I %ERRORLEVEL% gtr 0 goto forced_exit
goto exit
:forced_exit
echo Exit forced. Something went wrong.
exit /b 99
:exit

Will run any SQL command files suffixed .sql in the specified directory, in the directory sort order. If you’ve named your scripts: 01_dosomething.sql, 02_dosomethingelse.sql then you’ve got yourself the beginings of a very crude deployment system. There will be a .log file generated for each script that is run.

The parameters to sqlcmd are tweakable if you need remote connections. You might want to also tweak the options that dictate what SQL error level sqlcmd traps and passes on as its %ERRORLEVEL% if you really really need to stop on the first failure.