I am fairly new to SQL and have a great automatic way to do this:
I have 2 Servers, Windows 2003 Standard, both with SQL Server 2000 and sp3a.
I have a folder on the main server d:\mssql\data\backup\ - I made a folder here called "disasterbackupcopy".
I have a folder on the backup server d:\mssql\data\backup\ - I made a folder here called "disasterrecoverycopy".
Now setup job to run in SQL Agent, Jobs, here is what my job does:
--start of the script--
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK', no_output
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK', no_output
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK', no_output
EXEC xp_cmdshell 'DEL \\ufcw655hw2\DisasterRecoveryCopy\VHCS_HIPAA.BAK', no_output
EXEC xp_cmdshell 'DEL \\ufcw655hw2\DisasterRecoveryCopy\VHCS.BAK', no_output
EXEC xp_cmdshell 'DEL \\UFCW655HW2\DisasterRecoveryCopy\CMS.BAK', no_output
BACKUP DATABASE VHCS_HIPAA TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK'
BACKUP DATABASE VHCS TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK'
BACKUP DATABASE CMS TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK'
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
-- End of the Script --
Set your shares for all administrator rights.
Then I have a script that runs every day at 4:00am and for the start of each day I have a perfect backup of the .bak files - I can easily restore if needed if the main server dies. NOTE: I also everynight have Veritas Backup Exec v9 with SQL Agent do a full backup to tape. I have on the hour a transaction log created thru the day while users work.
The purpose of the about script and shares is only incase the main production server dies, I can be up pretty quick and the database from the previous day is sitting over there. 9 times out of 10, your server dies or crashes at night and its when you come in for the 8am work to start somebody yells, "I can't connect to the database!"