Backup sizes?

I have a maintenance plan set up so that we do a full backup every Sunday
night. We then do a differential backup every night at 11:00. Additionally we
do transaction log backups every hour from 5:00 in the morning until 9:00 at
night. I recently noticed that my differential backup was about 7 times the
size of my full backup. Does this make sense to anyone else? What could be
causing this? Thanks. MattHi
Are you appending the backup into a single file?
It is best to give every backup a new file, every time it is run.
Regards
Mike
"Matt" wrote:
> I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally we
> do transaction log backups every hour from 5:00 in the morning until 9:00 at
> night. I recently noticed that my differential backup was about 7 times the
> size of my full backup. Does this make sense to anyone else? What could be
> causing this? Thanks. Matt|||You need to specify the WITH INIT option otherwise it appends each backup to
the prior one. Why not simply do a FULL backup once a night?
--
Andrew J. Kelly SQL MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally
> we
> do transaction log backups every hour from 5:00 in the morning until 9:00
> at
> night. I recently noticed that my differential backup was about 7 times
> the
> size of my full backup. Does this make sense to anyone else? What could
> be
> causing this? Thanks. Matt|||Would it not be faster to restore with my current setup? If not, then I
probably will just go to a Full backup every night. Thanks.
"Andrew J. Kelly" wrote:
> You need to specify the WITH INIT option otherwise it appends each backup to
> the prior one. Why not simply do a FULL backup once a night?
> --
> Andrew J. Kelly SQL MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
> >I have a maintenance plan set up so that we do a full backup every Sunday
> > night. We then do a differential backup every night at 11:00. Additionally
> > we
> > do transaction log backups every hour from 5:00 in the morning until 9:00
> > at
> > night. I recently noticed that my differential backup was about 7 times
> > the
> > size of my full backup. Does this make sense to anyone else? What could
> > be
> > causing this? Thanks. Matt
>
>|||> Would it not be faster to restore with my current setup?
Au contraire. With your current setup, you have to first restore the latest database backup, then
the latest diff backup, then all subsequent log backups.
With Andrew's suggestion you just restore the latest database backup and then all subsequent log
backup (same number of log backups as above scenario).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
> Would it not be faster to restore with my current setup? If not, then I
> probably will just go to a Full backup every night. Thanks.
> "Andrew J. Kelly" wrote:
>> You need to specify the WITH INIT option otherwise it appends each backup to
>> the prior one. Why not simply do a FULL backup once a night?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>> >I have a maintenance plan set up so that we do a full backup every Sunday
>> > night. We then do a differential backup every night at 11:00. Additionally
>> > we
>> > do transaction log backups every hour from 5:00 in the morning until 9:00
>> > at
>> > night. I recently noticed that my differential backup was about 7 times
>> > the
>> > size of my full backup. Does this make sense to anyone else? What could
>> > be
>> > causing this? Thanks. Matt
>>|||Thanks to everyone for your help. I assume the only reason to use
differential backups is if you don't have a very big window to do a full
backup? Again, thanks. Matt
"Tibor Karaszi" wrote:
> > Would it not be faster to restore with my current setup?
> Au contraire. With your current setup, you have to first restore the latest database backup, then
> the latest diff backup, then all subsequent log backups.
> With Andrew's suggestion you just restore the latest database backup and then all subsequent log
> backup (same number of log backups as above scenario).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
> > Would it not be faster to restore with my current setup? If not, then I
> > probably will just go to a Full backup every night. Thanks.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> You need to specify the WITH INIT option otherwise it appends each backup to
> >> the prior one. Why not simply do a FULL backup once a night?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
> >> >I have a maintenance plan set up so that we do a full backup every Sunday
> >> > night. We then do a differential backup every night at 11:00. Additionally
> >> > we
> >> > do transaction log backups every hour from 5:00 in the morning until 9:00
> >> > at
> >> > night. I recently noticed that my differential backup was about 7 times
> >> > the
> >> > size of my full backup. Does this make sense to anyone else? What could
> >> > be
> >> > causing this? Thanks. Matt
> >>
> >>
> >>
>
>|||> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup?
Yes, something like that. Or disk space prohibits you from doing db backups with that frequency...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:B64881EC-6D15-4AFE-ABBF-47BFF7CBCA79@.microsoft.com...
> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup? Again, thanks. Matt
> "Tibor Karaszi" wrote:
>> > Would it not be faster to restore with my current setup?
>> Au contraire. With your current setup, you have to first restore the latest database backup, then
>> the latest diff backup, then all subsequent log backups.
>> With Andrew's suggestion you just restore the latest database backup and then all subsequent log
>> backup (same number of log backups as above scenario).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
>> > Would it not be faster to restore with my current setup? If not, then I
>> > probably will just go to a Full backup every night. Thanks.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> You need to specify the WITH INIT option otherwise it appends each backup to
>> >> the prior one. Why not simply do a FULL backup once a night?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> >> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>> >> >I have a maintenance plan set up so that we do a full backup every Sunday
>> >> > night. We then do a differential backup every night at 11:00. Additionally
>> >> > we
>> >> > do transaction log backups every hour from 5:00 in the morning until 9:00
>> >> > at
>> >> > night. I recently noticed that my differential backup was about 7 times
>> >> > the
>> >> > size of my full backup. Does this make sense to anyone else? What could
>> >> > be
>> >> > causing this? Thanks. Matt
>> >>
>> >>
>> >>
>>