The goal: At 7:30 AM each day, copy all files named ynot_backup_*.bak from the folder G:\sqlbackup on the database server to the folder G:\ynot_backup on one of the web servers. Both servers are running Windows Server 2003.
First, I came up with an xcopy command to copy the appropriate files: xcopy /Y g:\SQLBackup\ynot_backup_*.bak \\10.2.66.30\g$\ynot_backup.A few points of interest:
- This command uses a UNC path (a path starting with two backslashes) to access the web server across the LAN. Specifically, \\10.2.66.30\g$ refers to the default share of the G: drive on the web server, and I've configured the permissions, both in the share and in the file system -- to give write access to authenticated users. (This is safe, because the share is accessible only to authenticated users using a non-routable private IP address on a local area network.)
- The /Y flag tells xcopy to overwrite any existing files without prompting the user for confirmation.
The above xcopy command works fine when executed in a DOS window on the database server. But to be able to schedule it, two changes are needed.
- First, we need to give the full path to xcopy.
- Second, we need to execute the command shell, cmd.exe, passing it the /c flag and the command to execute.
The final step is to go to Control Panel | Scheduled Tasks and create a new scheduled task to execute the above command at 7:30 AM each day. The only trick here is choosing an appropriate account under which to run the task. I chose an administrator account that has sufficient permissions and whose password doesn't change too frequently -- because each time the password changes, we must inform the scheduled task of the new password.
Now all I need to do is keep an eye on the web server and make sure the whole G: drive doesn't fill up with old database backups!
