Backup Database using Maintanence Plan

• gupta1282

  Default port

  Points: 1465

  More actions

  September 21, 2009 at 3:09 am

  #226316

  Hi

  Is it possible to take full backup of two databases on different schedules

  by creating only one maintanence plan

  Let me Explain the scenario

  I have Two DB , DB1 And DB2

  I want to create a Maintanence plan that take full backup of DB1 on every Sunday and full backup of DB2 on everyday.

  This is the question that is asked by interviewer

  Thanks & Regards

  Ghanshyam Gupta

• Silverfox

  SSC-Insane

  Points: 23150

  More actions

  September 21, 2009 at 3:52 am

  #1056130

  gupta1282 (9/21/2009)

  ---

  Hi

  Is it possible to take full backup of two databases on different schedules

  by creating only one maintanence plan

  Let me Explain the scenario

  I have Two DB , DB1 And DB2

  I want to create a Maintanence plan that take full backup of DB1 on every Sunday and full backup of DB2 on everyday.

  This is the question that is asked by interviewer

  Thanks & Regards

  Ghanshyam Gupta

  Tricky question. I am using sql 2008, if you use the wizard, you can specify different schedules for sub tasks, so you could do a full backup and differential on different days. but you can only specify one schedule for each task. so in that way you cannot have different schedules for full backups.

  BUT. you can modify the maintenance plan after you have created it and add in another backup task for a different schedule.

  --------------------------------------------------------------------------------------
  [highlight]Recommended Articles on How to help us help you and[/highlight]
  [highlight]solve commonly asked questions[/highlight]

  Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
  Managing Transaction Logs by Gail Shaw[/url]
  How to post Performance problems by Gail Shaw[/url]
  Help, my database is corrupt. Now what? by Gail Shaw[/url]

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  September 21, 2009 at 3:52 am

  #1056131

  you could create one plan to run a DB2 backup daily with conditional logic in the T-SQL backup code to run a DB1 backup if the day is Sunday

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  September 21, 2009 at 7:52 am

  #1056229

  I'd use SteveB's suggestion. Add a T-SQL task, calculate a date, branch based on the result.

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  September 21, 2009 at 1:39 pm

  #1056464

  Using SQL Server maintenance plans, you have several options:

  1) Create separate sub-plans for each database. Each sub-plan can be scheduled separately.

  2) Use conditional logic on your dependencies and only perform the backup for DB1 if it is Sunday.

  3) Use Execute T-SQL Task and manually code the backup

  4) Other options...

  My preference for this kind of request would be separate sub-plans because they are a lot easier to manage.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs