SQL Back ups Checklists

A challenging question for expert DBA and a nightmare for invoice. I would say it something needed to be taken care precisely because the whole company is dependent on the DBA guys if something goes wrong. And top of that I got responsibly to design a whole new Database including EDI, maintenance , hardware , monitoring and software required this is only side it also include solution like clustering and mirroring. We will  keep out attention to the maintenance plan and I mean simple but a powerful plan that can handle a lot of the burden.

Here what, I implemented. This is only basic version. Its something invoice DBA would like to go with since it involves more GUI and less T Sql but my next post I would emphasis more on T Sql scripts. Since every DBA should know the main power and flexibility being offered through the script .
But for now let get going with SSMS and GUI stuff over here.

It is one of the most important tasks. Backup plan consist of many small sub tasks.

1.      Full Backup:

A full back up of the databases is taken daily at  some specific tme . An Alert will send to the concerns for successful completion or failure of the task to execute.

2.      Differential Backup:

A differential back up will be taken after 1 hour.  An Alert will send to the concerns for successful completion or failure of the task to execute.

3.      Transactional Log

A Transactional log will be taken after 10 minutes. An Alert will send to the concerns for successful completion or failure of the task to execute.

4.      Check db integrity

Database integrity need to be check daily for all databases (Needs to run off time only since this consume a lot of time and affect the performance).

5.      verify your backups

Backup of all databases need to be verify for integrity and consistency.

6.      Rebuild index

Rebuild Indexes will run once a week on Saturday morning if required. This will not be weekly activity but depends on the need of fragmentation which will kick in as soon as fragmentation level goes up to 30%. The percentage figure comes in as discussed in most forums where expert DBA gave their advice.

7.      Reorganize index

Rebuild Indexes will run once a week on Saturday morning. This will not be weekly activity but depends on the need of fragmentation which will kick in as soon as fragmentation level goes up to 30%. The percentage figure comes in as discussed in most forums where expert DBA gave their advice

8.      Update statistics

Update statistics will take place daily with the regular maintenance plan. This will increase query performance for the last saved data.

9.      Backup History Cleaning

Back up history cleaning will only run once a week and clear the history one weeks before the current week.

10. Maintenance History Cleaning

Back up history cleaning will only run once a week and clear the history one weeks before the current week.

11. Third Party Software

Back up taken by the third party software daily at  some specific time. Verify integrity and Back up taken by the third party software.

12. Monitoring Reports

Report covering different aspects of disk utilization, SQL IO and performance monitoring must be email every morning at some specific tme

13. Alerts

Alerts are setup for every job being completed or fail in the maintenance plan.

These alerts will come in the shape of Emails. 

Above is the maintain check list I perform. These all can be done via SSMS which makes it easy. Now most of the people thinking we almost got all the things answer is "NO" at various times you need custom scripts to go ahead and solve the issues or some solution only specific to current environment.

Do share your opinion it is valuable to me and how I can improve my posts to help a lot of people