problem with differential backup

Question

Hi,

I have a maintenance plan "MP1" in SQL server 2005 that scheduler for every day to get full backup from my phone book database.

But

I want to define a maintenance plane "MP2" that contains a monthly full backup and a daily differential backup that these differentials backup based on this full backup.

And I want to keep MP1.

The Issue Is: I Want To Make Sure That Every Differential Backup We Have Everyday Is Based On Full Backup We Have Specified In MP2 ,Not The Full Backup We Have Everyday With MP1.It's Important For Us To Isolate The Backups In These 2 Maintenance Plans.in MP1 We Want Just Full Backup For Everyday & in MP2 We Want a Full Backup (Created In First Day Of Month) And 29 Differential Backup Based On Full Backup Created in First Day On Month.

Is such a thing possible?

"With great thanks."

Answer 1

Differential backup captures what ever the modification happened on after the latest last full backup.

so if you have database backup that is happening daily then why you are approaching for the monthly backup?

Also incase if your database very huge then taking monthly full backup and differential backup can works for your.

Still if you want to do, then you can do it by defining the maintenance plan properly without conflicting to your day to day backup.

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 2

Not sure about your requirement. But in case you also want to keep MP1 plan, you can switch to COPY ONLY backups. By doing this your differential backups will be based on Monthly Full Backup from MP2 plan

Copy-Only Backups (SQL Server)

---

- Vishal

SqlAndMe.com

Answer 3

Hi there,

Not an issue. two separate plans MP1 with differential backup and MP2 monthly full backup works fine.

thanks

kumar

Answer 4

Dear Rama Thanks For Your Answer!!!

I Need More Explanation.You Mentioned :"Still if you want to do, then you can do it by defining the maintenance plan properly without conflicting to your day to day backup."Could You Please Explain For Me How Can I Define This Maintenance Plan Properly? I Need Detailed Information.Thanks Again For Your Support !!!!

---

Experience is the best teacher.

Answer 5

In the maintenance plan(after you did modify ) ->in the right hand side of design window-click on schedule Icon-

there you can see frequency : select what you need for ex-daily or weekly or monthly

for ex- incase if you selected Monthly then specify on which day of the months.

Brad’s Sure Guide to SQL Server Maintenance Plans
http://www.bradmcgehee.com/books/

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.