Sql-server – Database full backup,differential backup and log backup plan

backupsql servert-sql

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?

Best Answer

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.

Related Question