I've set up a maintenance plan to carry out:
- a nightly full backup (overwrites file)
- a 2 hourly transaction log backup (appends to 1 file separate to the full)
The database is in Full recovery mode.
The idea is to get the Tape backup agent to take a backup of the Full backup file each night after the SQL Server full backup finishes.
However I am confused as to how to set up the backup files for the transaction log.
What is the best practice for dealing with:
- Taking a regular trans log backup
- should it create separate files for each one?
- how would I get each backup off the server to tape at some point?
- Backing up the full db each night
- should it overwrite the file each time
- I've noticed that the backup file is quite large after 3 days of running even though it is set to overwrite. (DB=28Gb, nightly backup file = 58Gb) Why would this happen?
In summary, what are the best practices (in terms of file specifics) for setting up a Full and Trans Log backup plan and then backing those up to Tape?