Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 325

F sp_maintplan_delete_plan
F sp_maintplan_delete_subplan
F sp_maintplan_open_logentry
F sp_maintplan_start
F sp_maintplan_update_subplan
The Execute Package Task will execute the packages stored in the system database or
on the file system. The execution will use the File Connection Manager or existing OLEDB
Connection Manager to connect to the corresponding package.
Finally, the database maintenance plan tasks can be managed using own TSQL scripts using
the SSIS packages where Execute SQL Task or SQL Task to execute the corresponding SQL
script or stored procedure against the SQL Server. The SQL Task is commonly used to build
tables, query metadata, execute DDL commands, and insert and delete data in preparation
for other DDL or DML operations in data flow tasks. This will pass input and output
parameters to SQL scripts and stored procedures to capture the results in variables.
Implementing Piecemeal restore strategies
The availability and recoverability is important for any database as that helps, at any point in
time, to restore a database to a specific point-in-time. Also, to restore all file groups in that
database to the same point-in-time requires the database design capable of implementing
multiple file groups for a database. As the databases become larger and larger, the
infrastructure and technology must support the availability strategy. In such cases, restoring
an entire multi-terabyte database to a point-in-time could result in big outage. The online page
restore feature from SQL Server 2008 R2 will build a best strategy of multiple database files
availability in order to host very large databases and minimize the backup time.
Getting ready…
The following prerequisites are essential to use the online piecemeal restore feature:
F SQL Server 2008 R2 instance must be the DataCenter or Enterprise edition
F The SQL Server instance must be the 2005 version or higher
F The databases must contain multiple file groups that can be restored and recovered
in stages through a process known as piecemeal restore
F The databases can use any of these recovery models: SIMPLE , or BULK-LOGGED , or
FULL , but piecemeal restore is more flexible when BULK-LOGGED or FULL recovery
model is used.
F Download and extract AdventureWorks2008R2 from the http://
msftdbprodsamples.codeplex.com/ site