MSSQL Scheduled Tasks servers windows

Windows backup bat script using xcopy

Recently I had the need to create a bat script that could be executed by the Windows Scheduled Tasks. The purpose was to copy files from one server to another as a cheap way to backup files created by MSSQL backing up the databases. Here is the .bat file contents (cleaned up to protect sensitive data):

:: variables
set sourcedrive=D:\
set backupdrive=\\servername\d$
set backupcmd=xcopy /s /c /d /e /h /i /r /y
echo # # # Moving files
%backupcmd% "%sourcedrive%\directory_to_backup" "%backupdrive%\directory_to_store_backup"
echo # # Moveing Complete!

(NOTE: I am doing this backup via an internal network and using a user account that exists on both systems. Security may dictate that you handle this differently based on your circumstances.)

Notice that for the backupdrive I am calling another Windows server and using the d$. This would require that the Windows Scheduled Task be executed using a user that is trusted on both machines. Also you could specify a local directory on the same server if you did not need to copy the files to another server.