Maintenance plan and SQL server managed backups 1

[countyweb]

Hi All,

I have my databases with hosting company who provide a managed SQL backup.

In addition to this backup I am running a simple maintenance plan locally on the server to keep copies of my database for quick recovery. This way if I just need the database as it was the day before I can get it from the maintenance plan rather than waiting for the hosts to recover from tape.

They host company are telling me that when the maintenance plan runs it affects their managed backup and causes my incremental backups to be promoted to full backups - taking up loads of space on the backup.

Does this sound correct?
Does anyone know of a way to keep my maintenance plan and keep the managed backup only doing incrementals?
Thanks
c

 

[mrdenny]

Yes it is correct. You will want to let them manage all the backups, or have them backup your database file that you backup.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[countyweb]

Thansk for your reply mrdenny.
Out of interest, why does this happen?
Does the maintenance plan backup change the properties of database in some way that causes the managed backup to think it needs a full backup rather than a diff?
Thanks
C

 

[mrdenny]

Yes it does.

The way the SQL Server backups work is:
CoLo takes a full backup
You take a full backup
Colo takes a differential backup (using your backup as the base)
You take a full backup
Colo takes a differential backup (using your backup as the base)
You take a full backup
Colo takes a differential backup (using your backup as the base)


In order for the CoLo to restore your database they would need your full backup as that is what the differential is based on.

Some backup solutions will notice that the differential backup isn't based on the full backup that it has on hand they will trigger a full backup to be taken instead.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[countyweb]

Hi mrdenny,
Thanks for your answer.
much appreciated.
C

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[olahallengren]

What you could do is to perform your local backups with the option COPY_ONLY. This way you're not affecting the managed backup solution.

http://msdn.microsoft.com/en-us/library/ms186865.aspx

I do not believe that the Maintenance Plans have support for the COPY_ONLY option, so you need to use a T-SQL solution. I have a backup stored procedure that you could use if you like.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Ola Hallengren

http://ola.hallengren.com