SQL Server backup media set and file separation

Question

SQL Server backup media set and file separation If I am going to do such a backup plan under full recovery model for a particular database

1. Weekly full database backup
2. Daily differential backup
3. hourly T-log backup

Retention period: 28 days

How should I arrange the files? Should each and every backup have a unique file name?

I tried using SMSS to do a few and ending up all backups in a single test.bak file. I can see the content of the backup file test.bak contains a lot of backups including full backups, differential backups and log backups.

I know it wasn't right. What is the best practice for arranging backups into separate physical backup files?

Answer 1

I would say that there isn't necessarily a Best Practice, this is more of a matter of personal preference or your businesses operating procedures. I've had separate folders per database, then sub folders for FULL, DIFF, LOG at some jobs, others everything goes to a single folder and the backup name is some combination of database name, backup type, and datetimestamp to make each file uniquely named. I'd recommend looking at Ola Hallengren's maintenance solution:

https://ola.hallengren.com/

This is what I recommend to all my clients for database maintenance and backups and it handles everything dealing with the file naming and folder structure automatically for you. Just run his script and set the jobs to run at the frequency and schedule that meets your requirements and you are all done.