Friday, February 10, 2012

Backing up the Master, Model and MSDB databases

Hi,
I am new to SQL 2000 Server and as far as I have read, it seems that I
should backup the Master, Model and MSDB databases.
However, Should I...
1.) Backup the transaction log as part of the maintenance plan?
2.) Reorganize data and index pages?
3.) Remove unused space from database files?
4.) Check database integrity and should I include or exclude indexes? If
indexes are included/excluded, should I check to Attempt to repair any minor
problems and should I perform these checks before doing backups?
5.) Create a folder for each database?
6.) Remove files older than the default of 4 weeks or should I extend the
period?
Lastly,
7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should I
limit rows to 1k, 5k, 10k, 100k rows for this plan?
Thank You in advance,
DaveTry to give an answer to all of your numbered qustions, but remember that:
a) Is a good practice make a backup of your system database whenever you've
done some action that updates them; so, regarding the master, for example,
any CREATE, ALTER or DROP statement will surely update it, and make a backup
is a good idea; regarding the MSDB, each create, alter or drop of some Job,
Alert, Operator or any SSIS implementation, etc. will update it; regarding
the model, if you modify it could be a good idea make a backup.
About your qustions:
1) No for master database (you CANNOT backup transaction log of the master
DB!), optional for MSDB and model (but this means you apply a recovery
strategy, and you should carefully consider the interval between each backup)
2) No for master, optional for msdb and model; Oops: you can reorganise just
indexes, not data, considering clustered indexes as indexes and not data...
3)NEVER !!!
4)No for master, optional for msdb and model
5) and 6) As you like.
Generally, the recovery strategy for the system databases differs from the
one adopted for User databases, and a strategy too "regular" could be not so
efficient (suppose you plan a weekly - ora dayly - strategy, but on Tuesday
(for example) some big migration or some big deployment produces a lot of
updates on your system databases: I suggest a backup as soon as possible,
because a crash before the nightly batches could mean an important loss.
Gilberto Zampatti
"Dave" wrote:
> Hi,
> I am new to SQL 2000 Server and as far as I have read, it seems that I
> should backup the Master, Model and MSDB databases.
> However, Should I...
> 1.) Backup the transaction log as part of the maintenance plan?
> 2.) Reorganize data and index pages?
> 3.) Remove unused space from database files?
> 4.) Check database integrity and should I include or exclude indexes? If
> indexes are included/excluded, should I check to Attempt to repair any minor
> problems and should I perform these checks before doing backups?
> 5.) Create a folder for each database?
> 6.) Remove files older than the default of 4 weeks or should I extend the
> period?
> Lastly,
> 7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should I
> limit rows to 1k, 5k, 10k, 100k rows for this plan?
> Thank You in advance,
> Dave

No comments:

Post a Comment