I am new to SQL server management, I'm mostly a developer tasked with managing a SQL Server :-(

That aside, please bear with me.

Right now I have made a maintenance task to create a Full Backup every day at 2:00 am, and afterwards delete any backup older than 2 days. That however leaves a whole workday worth of data to be lost in case something went wrong at the right (wrong?) time. So to minimize data loss without without making performance take a hit, I', trying to create one weekly Full Backup and then a Differential Backup every 4 hours. I am however puzzled at how to do this from the management studio, or anywhere else for that matter.

So my question is:

How can I configure this backup 'policy' on my Server?

  • Check this links for Backup's creation: solutioncenter.apexsql.com/… community.ivanti.com/docs/DOC-42185
    – CR241
    Jul 17 '17 at 16:10
  • Ultimately, your backup plan needs to match your goals. If you're looking to minimize data loss, you may want to consider using the FULL or BULK_LOGGED recovery model, and transaction log backups. Those record all data changes since the last t-log backup; they're usually quick and you could schedule them to run every 15 minutes. Differential backups record all changes since the last full backup; If you have a full from Sunday, and diffs from Monday and Tuesday, you should really only need the Tuesday diff - it has everything from Monday in it as well.
    – RDFozz
    Jul 17 '17 at 17:05
  • Maintenance Plans include a Mainenance Cleanup task that can delete backup files. Full and Differential backups usually end with ".bak"; if you need to delete differentials and full backups separately, then the simplest solution via maintenance plans is to write them to different folders, so you can set different "Delete files older than" rules to the full and the diff folders.
    – RDFozz
    Jul 17 '17 at 17:20
  • Differentials typically end in .dff in most places I've seen :) Your mileage may vary, but its good to stick with the industry standard. Saves on needing an extra folder as well
    – Kevin3NF
    Jul 17 '17 at 17:29

Sounds like you are using maintenance plans for your Full backup. If so, make a new plan with a Backup task in it and change the backup type in the GUI to Differential, then set the schedule. As RDFozz says, consider also using t-log backups every 15-60 minutes throughout the day. If not, and your databases are in FULL recovery model, you will likely get crazy file growth in your .ldf files :)

enter image description here

Note that the task in this image says full, because that is the default. When I click OK, it will change.

  • 1
    Also, google "Accidental DBA" and you will find all sorts of places with articles and how-to stuff for people in exactly your situation.
    – Kevin3NF
    Jul 17 '17 at 17:15

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.