Daily, Weekly, Etc. Maintenance Procedures

[BudT]

Is there a good place to find a document that lists those procedures that should be done on a daily, Weekly, Monthly, etc. basis. We are new to SQL7.

 

[Bernadette]

Hi There

When you are deciding about your backups you really have to
decide
How important your data is and what is the cost to the company if it is lost.
How big are the databases and are they used heavily
Are all your databases residing on one server or on their own server.
You have to decide how often do you want to do full backups, differential (cumulative changes since the last backup

In our company we have 24 SQL servers but each of them only have about 2 databases on them. They are not used at nighttime and they are primarily SQL Servers with no other applications on them so I tend to do a full backup at midnight and Lunchtime. (If the information is ultra important you can do differential backups on the hour, or even backup the Transaction log between that. Although you have to remember that jobs slow the server down and you dont want to have them too often or the users will complain that SQL Server is slowing down ! You have to decide what "Point of Recovery" you want if you have a diaster.)

You can use the "Database Maintenance Tool" to set up jobs for database Integrity checks and optimisation.
You can run this maybe once a week .. say weekends. Another good idea is to keep your eye on the SQL Server Error Log and the NT Event Log to check if the database is writing any errors to that.

2 simple SQL Server books I would recommend are

"SQL Server System Administration - Baird & Miller
ISBN: 1-56205-955-6

"SQL Server 7.0 Administrators Pocket Guide" - Microsoft
ISBN - 0-7356-0596-3

They are both simple and good for starting off. They explain everything about being a DBA, Diaster Recovery, Backups, Security etc very clearly. They are not very expensive either.

Hope this was of some help to you.
Good Luck with SQL Server 7.0

Bernadette S-)

 

[jrc1]

Does anyone know if the set-up options/schedules for a datbase maintenance plan(optimization, back-up, integrity, etc) are stored in a table somewhere in the datbase? I want to be able to define a query that pulls maintence plan information. For example I want to query a database and see if the "back up the database as part of the maintenance plan" is checked and the scheduled time.

 

[Bernadette]

Hi There,

The tables you are looking for are in the msdb database. They are ...

msdb..sysdbmaintplans
msdb..sysdbmaintplan_jobs
msdb..sysdbmaintplans_databases
msdb..sysdbmaintplans_history

Hope this helps

Bernadette