Building an automated script based Backup / Maintenance Solution

Question

Guys,

My company at present has the following Setup:

127 SQL servers (2000 and 2005) and over 700 databases. We are in the process of server consolidation and are planning on having a Master server / Target servers setup to enable centralized administration. As part of this project, I have been given the responsiblity of creating a script based automated backup / maintenance solution.

Thanks to Ola Hallengren's script available here I have made a lot of progress.

Here is what I plan:

• I have a database in the master server which has details of SQL instances, databases and backup path details.
• I am in the process of modifying Hallengren's script to read from this database and create jobs dynamically.
• Users are allowed to define what kind of backup they want, how often and how long the backup needs to be kept.
• I am also looking at having the ability to spread out jobs, so that I do not have too many jobs running at the same time.
• My thoughts are to create tables that have the data needed to be passed as parameters to sp_add_job, sp_add_jobstep and sp_add_jobschedule.

Please share your experiences in the pitfalls and hurdles with this setup. All ideas welcome.

Thanks,

Raj

Answer 1

You might also consider the approach of creating a full backup job and a transaction log backup job on each server, which retrieve the databases from the master database and feeds them into a procedure for backup. You could run the jobs every 5 minutes and the procedure would have to work out what time it is and what type of backup is required.

I say this, because things could get messy creating massive amounts of jobs in an automated manner - but maybe you have it worked out well. Be sure to create a 'primary key' with the job name if you take your original approach.

Spreading out jobs should be easy if you keep record in the database and find available windows to put new jobs in. I've seen scripts for this.

You could also do this in a SSIS package that runs from your admin server. It would iterate over the databases and connect to servers to perform the backups.

Check out the first 3 article here: http://www.sqlmag.com/Authors/AuthorID/1089/1089.html

Answer 2

Moving to 2005 we were not happy with the Intergration Services Maintenance Plans

We wrote sp's to do Full and Tran Backups as well as reindex, arrgegate management, archiving etc

A Daily and Hourly Job would step thru master..sysdatabases and in a try catch block apply the necessary maintenance. Would not be hard to read from any table and check user defined conditions.

Unfortunatedly there was no way to tie output to msdb..sysjobhistry, so we logged to central table. The upside was we had a lot more control over what was logged. However would be simpler to read than going thru 700 jobs.

Answer 3

Thanks for the help Guys. We are through with this and are now testing the solution.

Raj

Answer 4

Here's how Yardi Genesis handles the similar task. On each of the hundreds of DB servers they have, they run SQLBackupAndFTP with "All Databases" settings. Then they login to a control panel on SQLBackupAndFTP.com to track success/failure of backup jobs on all of the servers