[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

SQL server 2008 Maintenance plan.

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.

  • 3
  • 2
2 Solutions
Ephraim WangoyaCommented:
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.
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...
DaeraAuthor Commented:
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?
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

DaeraAuthor Commented:
Above issue sorted by doing a full-backup and straight after running the transaction-logs maintenance plan.
Yes, log backups needs an already existing full backup.
DaeraAuthor Commented:

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now