RE: What is the best practice to create Maintenance Plans

From: Abel Chan (awong_at_newsgroup.nospam)
Date: 01/04/05


Date: Tue, 4 Jan 2005 10:36:32 -0800

Hi Sophie,

Thanks so much to your detail response. I will look through your
suggestions and links. If I got more question, I will let you know. Thanks
again.

Abel

"Sophie Guo [MSFT]" wrote:

> Hello Abel,
>
> There is not a definitive answer to the question of which backup is better.
> The answer depends on your real situation.
>
> In order to develop a successful backup and restore plan, you must
> understand when your data needs to be accessible and the potential impact
> of data loss on your business. Answering the questions in the following
> article can help you determine your availability requirements and
> sensitivity to data loss. Then you can choose the correct Microsoft® SQL
> Server™ 2000 recovery models for your databases and make the necessary
> technical and financial tradeoffs:
>
> Analyzing Availability and Recovery Requirements
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _bkprst_27qd.asp
>
> If master is damaged in some way, for example because of media failure, an
> instance of Microsoft SQL Server™ may not be able to start. In this event,
> it is necessary to rebuild master, and then restore the database from a
> backup.
>
> To prepare for disaster and recover from a disaster, please refer to the
> steps in the following article:
>
> Planning for Disaster Recovery
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _bkprst_27qd.asp
>
> With the Simple Recovery model, the database can be recovered to the point
> of the last backup. However, you cannot restore the database to the point
> of failure or to a specific point in time. To do that, choose either the
> Full Recovery or Bulk-Logged Recovery model.
>
> To answer your questions:
>
> Question:
>
> How should I create maintenance plans to backup and protect the integrity
> of my data.
>
> Answer:
> If you have enough disk space, I recommend you perform the following backup
> strategy:
> " Full Database backups.
> " Differential backups.
> " Transaction log backups.
>
> To recover in the event of media failure
> 1. Back up the currently active transaction log. For more information, see
> Transaction Log Backups.
> 2. Restore the most recent full database backup without recovering the
> database.
> 3. If differential backups exist, restore the most recent one.
> 4. Restore each transaction log backup created since the database or
> differential backup in the same sequence in which they were created without
> recovering the database.
> 5. Apply the most recent log backup (created in Step 1), and recover the
> database.
>
> Important: To protect against loss of transactions under the Full Recovery
> model, the transaction log must be protected against damage. It is strongly
> recommended that fault-tolerant disk storage be used for the transaction
> log.
>
> For more information, please refer to the following article:
>
> Full Recovery
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _bkprst_60s9.asp
>
> Question:
> Should I create one for master, one for A and one for B?
>
> I also find someone create maintenance plan by combining the master
> database with database A or B. What is the benefit/reason for that?
>
> I found a problem with combining master with another database is that the
> transaction log won't be able to back up because of master's Recovery Model
> Simple setting. Correctly me if I am wrong on this.
>
> Answer:
> Transaction Log backup for master database is not allowed. Please refer to
> the following article:
>
> 285288 PRB: Transaction Log Backups of Master Database Are Not Allowed
> http://support.microsoft.com/?id=285288
>
> It is suggested that using a separate maintenance plan for master database
> if you want transaction log backup for other database. Please refer to the
> following article:
>
> 303229 PRB: Transaction Log Backup Job Created with Database Maintenance
> http://support.microsoft.com/?id=303229
>
> 290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
> http://support.microsoft.com/?id=290622
>
>
> Question:
> Should I also change Recovery Model to Full for database A and B before I
> create the maintenance plan? Currently I have both in Simple? Do I need
> to restart the databases after these change?
>
> Answer:
> If you plan to use Full Recovery model, you need change Recovery Model
> before you create the maintenance plan. When a database is created, it has
> the same recovery model as the model database. The following information is
> for your reference:
>
> Selecting a Recovery Model
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _bkprst_27qd.asp
>
> You can switch a database from one recovery model to another in order to
> meet changing business needs. You don't need to restart the databases after
> these changes. The following information is for your reference:
>
> Switching Recovery Models
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _bkprst_27qd.asp
>
> For more inforamtion, please refer to SQL Server 2000 Books Online (Updated
> 2004)
>
> http://www.microsoft.com/sql/techinfo/productdoc/2000/
>
> I hope above information is helpful.
>
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>