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