What is the best practice to create Maintenance Plans

From: Sophie Guo [MSFT] (v-sguo_at_online.microsoft.com)
Date: 01/04/05 

Date: Tue, 04 Jan 2005 08:16:36 GMT

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.

Relevant Pages

  • RE: What is the best practice to create Maintenance Plans
    ... > If master is damaged in some way, for example because of media failure, an ... > With the Simple Recovery model, the database can be recovered to the point ... > Full Recovery or Bulk-Logged Recovery model. ... > Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: Log file is increeasing too much
    ... If you are using the FULL recovery model, you would need to backup the log ... Need smaller SQL2K backup files? ... > i have a database and a proccess which delete everything from database and ... > backup log mydatabase with truncate_only ...
    (microsoft.public.sqlserver.server)
  • Re: Sharepoint and SQL Recovery Model
    ... SQL Server 2000 uses recovery models to help you plan backups. ... You can use either Simply or Full Recovery Model based on your need. ... be recovered to the point of the last backup. ... The SharePoint Portal Server 2003 configuration database. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: transaction log
    ... It seems your Recovery model set to that database is "FULL". ... So we need to scdule a transaction log backup regularly (atleast hourly ...
    (microsoft.public.sqlserver.server)
  • Re: How can I assign the result of dateadd to a variable ?
    ... It then stays in the database until 4 days later when it ... I now check the "Check Database Integrity" option with "Include Indexes", ... I have now changed the recovery model to "Simple" (In Enterprise Manager, ... > modifications since your last backup? ...
    (microsoft.public.sqlserver.programming)