none
SQL Maintenance Tasks

    Question

  • Hi ,

    I am not getting backup at 0000 (Midnight) hrs If i use below time frame

    Maintenance Plan1.

    2:00 AM to 8:00 AM Full Backup on D drive

    Maintenance Plan2.

    8:00 Am to 02:00  PM on E Drive

    Wednesday, November 06, 2013 9:11 AM

Answers

  • Hi,

    Please ignore above one.

    My existing maintenance plan performing daily full backups for every 2 Hrs. (SQL 2005)

    Scheduled : 4:00:00 AM

                        3:59:00 AM. 

    Maintenance Clean up task: Oder than 12 Hrs

    But the problem here is we are facing disk-space issue.

    Now my plan is to split above plan into 2 maintenance plans, in that  some file should go to D:\ and some files should go to E:\

    Now tel me how to schedule.

    Vijay ,

    Same question in below thread

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d999c6d4-350f-4da4-a7b2-faa2e11c8869/maintenance-plan?forum=sqldisasterrecovery

    >>But the problem here is we are facing disk-space issue.

    Unfortunately disk space issue is something you have to take care of by increasing space.I dont know what need is there to take full backup every 2 hrs.You should try to convince your manager that it is because of every 2hrs full backup disk is getting full.

    Explain him its trn log backup which will enable you point in time recovery not full backup


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, November 06, 2013 10:02 AM

All replies

  • Hi,

    Can you clarify your question?

    When you say "2am to 8am Full backup", what exactly do you mean?  Have you scheduled your full backup to start at 2am and your window is until 8am?

    Neither of those schedules (2am to 8am, and 8am to 2pm) span midnight anyway.



    Thanks, Andrew
    My blog...

    Wednesday, November 06, 2013 9:29 AM
  • Hi,

    Please ignore above one.

    My existing maintenance plan performing daily full backups for every 2 Hrs. (SQL 2005)

    Scheduled : 4:00:00 AM

                        3:59:00 AM. 

    Maintenance Clean up task: Oder than 12 Hrs

    But the problem here is we are facing disk-space issue.

    Now my plan is to split above plan into 2 maintenance plans, in that  some file should go to D:\ and some files should go to E:\

    Now tel me how to schedule.

    Wednesday, November 06, 2013 9:52 AM
  • Hi,

    Please ignore above one.

    My existing maintenance plan performing daily full backups for every 2 Hrs. (SQL 2005)

    Scheduled : 4:00:00 AM

                        3:59:00 AM. 

    Maintenance Clean up task: Oder than 12 Hrs

    But the problem here is we are facing disk-space issue.

    Now my plan is to split above plan into 2 maintenance plans, in that  some file should go to D:\ and some files should go to E:\

    Now tel me how to schedule.

    Vijay ,

    Same question in below thread

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d999c6d4-350f-4da4-a7b2-faa2e11c8869/maintenance-plan?forum=sqldisasterrecovery

    >>But the problem here is we are facing disk-space issue.

    Unfortunately disk space issue is something you have to take care of by increasing space.I dont know what need is there to take full backup every 2 hrs.You should try to convince your manager that it is because of every 2hrs full backup disk is getting full.

    Explain him its trn log backup which will enable you point in time recovery not full backup


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, November 06, 2013 10:02 AM
  • That's fine.

    Mean while i am looking for second option.

    Please suggest me.

    How to schedule.

    In this thread i didn't get answer what i expected.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d999c6d4-350f-4da4-a7b2-faa2e11c8869/maintenance-plan?forum=sqldisasterrecovery

    Wednesday, November 06, 2013 10:35 AM
  • If you're running full backups every 2 hrs and clean up backups older than 12 hrs, and it's causing you to run out of disk space, you have the following options:

    - Reduce backup frequency, so you're storing less full backups

    - Increase cleanup schedule so that you keep less backups

    - Ditch the full backup every 2 hrs and use differential + tlog backups

    - Use backup compression features

    - Move backups periodically to another location

    - Expand your backup drive, or move your backup default location to another larger drive

    I don't understand your requirement for 2 hr backups either


    Thanks, Andrew
    My blog...

    • Proposed as answer by Jason Brugger Wednesday, November 06, 2013 4:22 PM
    Wednesday, November 06, 2013 10:47 AM
  • Hi ,

    As per my company policy

    ----It is not possible to reduce backup frequency

    ----It is not possible to increase cleanup schedule

    ----It is not possible to maintain differential backups

    ----It is not possible to move backup to another location.

    We are using SQL 2005, so compression is not possible and there is no provision to expand backup drive.

    As per my knowledge the only option is split maintenance into 2.

    Wednesday, November 06, 2013 11:07 AM

  • As per my knowledge the only option is split maintenance into 2.

    Then create 2 different maintenance plan for 12 hrs each.One will execute from 00:00 AM  to 11:59 AM backup file location will be D drive.

    Second MP will start from 12 PM to 11:59 PM and location of back file would be E drive.For each in MP add a clean up task .

    I don't think starting backup at midnight or at 4 AM will actually matter as anyhow every 2 hr you are taking backup

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, November 06, 2013 11:19 AM
  • Hi

    I am not worrying about start time.

    But the files should include 0000.bak hrs. (Midnight)

    Because every day we are moving midnight backup file (0000.bak) to our DR site.

    Wednesday, November 06, 2013 12:20 PM

  • But the files should include 0000.bak hrs. (Midnight)

    Because every day we are moving midnight backup file (0000.bak) to our DR site.

    What do you mean by this?

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, November 06, 2013 12:26 PM
  • For example:

    test_backup_201311060000.bak

    Here 0000 means  12.00 AM.

    Wednesday, November 06, 2013 1:14 PM
  • Do you happen to know the Recovery Mode of your databases? Do you have Log backup in place for these databases if these databases if the Recovery Mode is FULL? How big are these databases?

    You need to understand many factors about database backup to find a suitable maintenance solution. Please share some more information. Thanks.

    Wednesday, November 06, 2013 4:03 PM