0

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?

4

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.

  • Jonathan, thanks for the reply. Once you separate the backup files do they share the same MEDIANAME? – Shawn Feb 2 '16 at 17:38
  • No, they typically don't use the MEDIANAME at all. It is not required and is optional. I looked at the dynamic SQL for Ola's scripts and it doesn't use that at all. – Jonathan Kehayias Feb 5 '16 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.