I had a client call and inform me that their TSM (Tivoli Storage Manager) backup agent wouldn’t work on their database server. This essentially defeats the purpose of TSM but they asked if I could simply copy the database and hourly transaction log backup to another server. I’m a big fan of batch files, but with something as important as a SQL backup, I decided to just create a simple job in SQL 2005.
I called the new job: “Copy from Server1” (where Server1 is your database server) and created 3 steps.
1) Delete prior day BAKs – This simply deletes the backups from the prior day. Set this to the desired number of days for the desired number of backups you wish to keep. For your ‘Type’ (in the job step), select Transact-SQL script (T-SQL). For the command, use the following:
xp_cmdshell ‘del \\Server2\c$\SQL_BU\database_name\*.bak’
2) Delete Prior Day T-Logs – We’re simply doing the same thing for the transaction log files.
xp_cmdshell ‘del \\Server2\c$\SQL_BU\database_name\*.trn’
3) Copy Files Over – This time, choose the type of ‘Operating system (CmdExec)’, Run as ‘SQL Agent Service Account’ and use this command:
xcopy \\Server1\c$\SQL_BU\database_name_back* \\Server2\c$\SQL_BU\database_name /E /I /F /H /Y
As you can see.. it’s just a simple xcopy command that we would typically use in a batch file. But.. by using a SQL job, we can let SQL alert us of any problems and the failure rate is minimal compated to a batch file setup.