Skip to main content
Database Administrators

Return to Question

Added a tag
Link
edit approved Dec 19, 2019 at 22:44
Ola Ström
edit approved Dec 19, 2019 at 22:44
Ola Ström
  • 301
  • 1
  • 3
  • 10
Post Migrated Here from stackoverflow.com (revisions)
Source Link
LONG
LONG
  • 131
  • 4

Database full backup,differential backup and log backup plan

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...

  1. 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?