2

I am working with DB backup, first I would like to get a roughly plan on weekly/daily backup. I have countered some problems when I am trying to get such a plan, What I tried is:

For weeklyFULL DATABASE BACKUP, using: BACKUP DATABASE name To Disk... WITH INIT,SKIP;

  1. Can I set it to SKIP and overwrite the old full backup file every week? If I did not add EXPIREDATE or RETAINDAYS, does that mean the current backup will not overwrite the existing one because the old one never expires?

    For daily Differential DATABASE BACKUP, using BACKUP DATABASE name To Disk... WITH DIFFERENTIAL,INIT,SKIP;

  2. How does the SQL Server know which full backup is daily differential based on? By using WITH FORMAT, MediaName='...'? If so, does that mean for the first time I do the full database backup I need to format a new media set such as DatabaseA set for that database, and for the later back up, I could use WITH NOFORMAT, medianame = DatabaseA' to let sql server knows that differential backup should be attached to that full backup database?

    For transaction log backup, using BACKUP LOG A TO DISK...

  3. I know it is not good to add WITH INIT to log backup, but how to handle the increasing size of log backup? Shrinkfile(log)each time I did the full database backup? Does that mean I could ignore the increasing size of transaction log before the next full backup comes?

Improve this question
3
  • Any suggested frequency for each type of backup? For example: Weekly Full backup; daily differential backup; daily log backup? shrinkle every week?
    – LONG
    Jan 23, 2017 at 19:21
  • Most of the options you're asking about are only really relevant when backing up to tape, are you doing that, or to disk?
    – Gareth Lyons
    Jan 24, 2017 at 13:43
  • Just disk, not tape
    – LONG
    Jan 24, 2017 at 14:30

2 Answers 2

Reset to default
3

I'm going to try to save you a lot of time messing with outdated and mostly unnecessary backup options. Just use Ola Hallengren's free scripts.

Next up, deciding backup frequency is a business decision as it relates to RPO and RTO. No one here can tell you how much data you're allowed to lose. One thing though: if you're thinking about taking log backups once a day, you shouldn't be in full recovery model.

That also relates to: DON'T SHRINK YOUR DATABASE. Your log file grows because you don't back it up either at all, or often enough. If you shrink it, it will grow again, and that's just not a smart way to administer a SQL Server. Either back it up more often, or switch to SIMPLE recovery.

That decision is between you and management, though.

Improve this answer
0

To add to all the other answers, as for the frequency of different backups, if the database is mission critical and so in FULL recovery model then a weekly FULL backup, daily DIFFERENTIAL backup and a LOG backup every 15 minutes will be the best possible solution. For less critical database, go for the weekly full and daily DIFFERENTIAL backup.

Improve this answer
2
  • This isn't true at all, if they have RPO less than 15 minutes. Taking log backups every 15 minutes means they can lose 15 minutes of data.
    – Erik Darling
    Jan 24, 2017 at 23:29
  • Yes that makes sense...so log backups should be decided w.r.t the RPO. But we still can recover if we can grab a tail log backup. Moreover the guy asked for a backup plan, so I thought of giving one.
    – user3300750
    Jan 25, 2017 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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