SQL server 2008 Maintenance plan.

Posted on 2012-09-12
Last Modified: 2012-09-13
Hi EE,

I'm trying to create a Maintenance plan that will work well for my environment. I'm new to SQL so any help/suggestion is most welcomed.

Here is what I have:

DB1 and DB2 are business critical databases. DB2 is tied to DB1. I can restore the two databases separately. Both DB1 and DB2 are hosted on a 2 node cluster.

I need a good backup plan so that I can restore from any point in time when needed. I expect to loose at the worst-case scenario only an hours work.

My tentative backup plan for now is below so help me have a better plan.
-Differential/every lunch time
-Transaction log/hourly everyday
-cleanup/once a week

Restore plan.
If there is a disaster and I need to restore the database, I expect to only loose less than an hours worth of data.

What will be a good restore plan to achieve this? My backup plan should enable me to do this restore accordingly.

Now I do recognize that my  transaction logs will get huge. How do I clean it up. What is a good time frame to clean up transaction log, considering my requirements. Can this be incorporated in the Maintenance plan.

All in all I want to have a plan where I can only afford to lose up to an hour of transaction in the worst case scenario.

Please advice on how go about this.

Question by:Daera
    LVL 32

    Assisted Solution

    Why dont you make the transaction log backups more frequent than that?
    Loose only 15Minutes worth of work, I'm assuming you dont have alot of transactions going on.

    I would go with a Weekly Full Backup - Friday at end of business day.
    Daily Differential Backups
    15 minute interval transaction log backups

    As long as you are doing frequent transaction log backups, the log file will not grow out of hand.

    You will need to set up three maintenance plans, one for each respective backup and schedule them accordingly.
    LVL 15

    Accepted Solution

    The frequency and the type of backup is depend on your database size and Backup drive space, if you are small DB you can have daily full backup every day and if your DB is huge, you can schedule the full backup weekly, twice in a week or once in a month or so...

    Differential backups helps you to reduce the no of log files to be restored in the event of failure, so your Diff and Log file backup plan looks good.

    cleanup/once a week
    This something that you should plan carefully by analyzing free space in the backup destination device, if you have plenty you can retain the backups for longer period.

    The key thing to note down here is
    The backup destination should be in separate location, not in your server itself, so that you can easily recover in the event of any disaster to your server.
    You should have an alternate copy of the backup and that should be stored outside the data center, this helps you to recover the data in the event of any disaster to data center.
    Should regularly validate the backups by performing restore in a separate server.
    Make use of the advantage of Backup Compression in enterprise edition.
    Perform a backup validation, when the backup is completes.
    When you do a restore, remember to take tail log backup, this captures the committed changes after the last transaction log backup.

    Now I do recognize that my  transaction logs will get huge. How do I clean it up. What is a good time frame to clean up transaction log, considering my requirements. Can this be incorporated in the Maintenance plan.

    You should size your transaction log file properly, so that it could accommodate the largest transaction and its rollback + the index rebuild. Once this is sized properly, frequent log backups will keep the transaction log file size compact, this happens by reusing the unused VLFs in the log file. So the key is frequent log backups, Other things to consider is if you have any open transaction, or Mirroring , tranasactional replication, sometimes they delays log reuse, you can check this by using the following query -

    SELECT log_reuse_wait_desc FROM sys.databases

    You can trust the maintenance plan ships with SSMS, you can use this for backup scheduling else you can have your custom scripts or third party tools for your backups.

    You are good to go...

    Author Comment

    Thanks for the responses. I have tried to set my maintenance plan and did a separate backup to transaction log and it is giving me this error:

    BAC..." failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
    BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    I'm getting a bit confused with the error message as I did backup the database in a separate plan and it is current. Any help on this?

    Author Comment

    Above issue sorted by doing a full-backup and straight after running the transaction-logs maintenance plan.
    LVL 15

    Expert Comment

    Yes, log backups needs an already existing full backup.

    Author Closing Comment


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now