SQL Server 2005 - adhoc script to perform backup, restore and integrity checks/optimization jobs without Database Maintenance plans?
As a DBA you might be aware that since SQL Server 2005 database maintenace plans have given flexibility in deploying the workflow of tasks required to make sure that your database is optimized, is regularly backed up, and is free of inconsistencies in addition to the avoiding the fragmentation on indexes with optimization tasks.
This may be a good solution of smaller farm of databases deployment, but there is more to do with large-scale databases deployment such as adhoc mode of optimization and integration tasks to the selective databases, which is not possible to use with DB maintenance plans as you can create the core maintenance plan, but creating plans manually gives you to choose the number of databases or set of databases you have to take care. Again for the inclusion of database on selective purpose it may not be feasible to create 'n' number of plans that can confuse the way of managing plans in longer term. By default you have to be expert in SSIS as this maintenace plan internally uses Integration Services package, which is run by a SQL Server Agent job.
Its worth to mention that for the database BACKUP strategy (for all) you can easily deploy the maintenance plan route that will ensure to continue this important aspect of DBA responsibility, within my end I have no problem managing more than 1000+ databases via this process where I have differentiated the plans according the size of databases, such as database between 0 and 5gb will have 1 plan, between 5 and 15 gb databases would have seperate maintenance plan and so on. Also these plans will be running at different times in order to ensure they complete successfully and server is not hammered with this process at the same time due to the historic issues we have had on this database server.
During the recent times on the forums & newsgroups I have seen many people asking for a script or solution to perform the adhoc and selective process of integrity checks & optimization, as it is not that feasible to peforming with maintenance plan route. In this regard I have found an excellent blog entry from Ola Hallengren about performing SQL Server 2005 - Backup, Integrity Check and Index Optimization tasks with a script. I recommend to download and test his scripts in order to deploy flexible solution within your database environment.