Solved

Will a differential SQL Server 2008 backup job work this way?

Posted on 2011-03-24
6
540 Views
Last Modified: 2012-05-11
I setup a maintenance plan to just backup databases. I selected only differential, expire after 7 days. It ran for the first time last night and most of the databases were backed up (full). Some however said they failed because they needed a full first. So my questions are as follows:

1. If I run a manual full backup on the databases that did not get backed up will the subsequent differential backups work?

2. Will the old backups be deleted after 7 days or does the expire mean something else? I believe I need to add the maintenance cleanup task to delete files but would like confirmation.

Basically the goal is to always have 7 days of backups, one of which is a full and the rest differential. Nothing more, nothing less, and no manual interaction needed to keep it that way.
0
Comment
Question by:ThorinO
  • 3
  • 3
6 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 500 total points
ID: 35207666
1.  Yes.  Differentials and trans log backups have to have a full first, otherwise they've nothing to backup.
2.  Yes, they'll be removed.
0
 
LVL 10

Author Comment

by:ThorinO
ID: 35207689
Most of the databases ran fine and created a full backup, I'm not sure why only a few did not run, do you have any thoughts on that?

2. So the expire will take care of the removal and I don't need the cleanup task?
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 35207755
The cleanup task should take care of it.

The ones that did not run,  you'll just need to take a full backup of them (and make sure you do regular full backups from here out).
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Author Comment

by:ThorinO
ID: 35207780
Can I do full/differential in 1 maint plan or do I have to have full and a diff plan setup and the full go before the diff
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 500 total points
ID: 35207828
so, a differential is everything that has happened since the last full backup.  So, a full has to be taken before the differential.  

Often, a full backup is take every night or so, and a differential is taken a couple times throughout the day.
0
 
LVL 10

Author Comment

by:ThorinO
ID: 35352850
I created a new job that runs a full and differential, still watching it to see if it is working the way I want.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 18
SQL Help 27 45
SQL Server 2012 r2 - Sum totals 2 25
any adverse effect when modifying data type on a busy table. 4 13
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question