poor mans archival of backup files and tran logs to alternate location

,

you have to create backups with unique filenames or the files will get overwritten, so the sql script generates that, part. then you have to create a batch job on both source and destination servers and run in a scheduled task. the first batch file copies only new backups to the remote server, usually a backup or disaster recovery server. The second scheduled batch file deletes files older than -d -7 days . so you always have 7 days of backups and transaction logs.

 

email if you need assistance, works great for me.

genehunter29009@gmail.com

--backup job to create backups with date and time stamp

DECLARE @filename NVARCHAR(4000)
SET @filename = 'J:\Backup\baandb\baandb'+REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 100),':','_'),' ','_')+'.BAK'
exec master.dbo.xp_backup_database @database = N'baandb', @GUID = N'8D971BFB-1995-4B64-B206-A3291266BB12', @filename = N'J:\backup\baandb\baandb.BKP', @backupname = N'baandb backup', @desc = N'Backup of baandb', @init = 1, @logging = 0, @with = N'SKIP', @with = N'STATS = 10'


--also automate transaction log backups to the same folder 

--then setup a batch file to copy files every 30 minutes and email using blat if the job fails.
--This runs on database server every 30 minutes as a batch job scheduled
-- /E/D means do not copy files that already exist

xcopy j:\backups\baandb\*.trn \\yourservername\USBackups\ /E/D
if not errorlevel 0 (blat.exe j:\backups\backupsfailed.txt -to genehunter29009@gmail.com -subject "US Transaction logs archival failed")

Then on the backup location run this in a batch job to clear files older than so many days old (-d 7 = 7 day old)

forfiles -p "R:\USBackups" /s /m *.* -d -7 -c "cmd /c del @path"

you will have a poor mans backup that will automatically email you if it fails and also hold backups for so many days on second server.

Rate

4 (4)

Share

Share

Rate

4 (4)