SQL Backup Loop
Posted on 2013-01-09
Currently, I make a FULL backup and the log files prior that just completed full backup are deleted. Log files backups are then created and accumulate until the next full backup is completed and then they are erased. The loop begins again with a FULL backup and zero log backups. I would like to alter this loop to be:
1. Full Backup = run Monday morning
2. All log file created = run all throughout Monday
3. Full Backup = run Tuesday morning
4. All Log files created = run all throughout Tuesday
I would like step 3 and 4 to run while leaving the results of step 1 and 2 intact. When I loop back through 1 and 2, steps 3 and 4 are left intact. This would resolve the risk of my currently having a single BAK file as my 1 and only backup. If 1 is corrupted, at least i have 3. If 3 is corrupted, at lease I have 1.
I am not clear how to create such a LOOP? Everything is done in T-SQL. I do not use the maintenance wizards or rely upon expiration dates of backup files.