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.
>
>



Relevant Pages

  • Re:logfiles grow non-stop in SQL2000
    ... Looks like the recovery model for your database is FULL or BULK_LOGGED.Please change the recovery model for the database to ... If you do not have the 65 G to take a backup or if you do not want the transaction log backup. ... The logfiles for the most frequently-used databases grow non-stop. ...
    (microsoft.public.sqlserver.setup)
  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... 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: 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 ?
    ... > Dear David and Hugo, ... > I tried my DeleteInbatch SP on a database with about 600,000 records. ... The recovery model determines what kind of information is logged in the ... database log file(s) and how long the log file sticks around: ...
    (microsoft.public.sqlserver.programming)