Maintenance Plans Tips

I have changed the password of one of my SQL Server logins used for SQL Server Maintenance Plans. Everything was okay until a SQL Server Agent Job responsible for database backups failed. The SQL Server Agent Job was created by the Maintenance Plan Wizard. In this tip I will show you how to synchronize the password change in the SQL Server maintenance plan.

I have read the tip about securing SQL Server backups with a password. It is great option and I am planning to implement it for my production servers on SQL Server 2005 and SQL Server 2008. I am currenlty using a backup plan configured through maintenance plans, but I want to be able to create password protected backups. In this tip I will show you how this can be done.

For better or for worse, the most common tool for performing database maintenance in SQL Server 2000 is the Database Maintenance Plan Wizard, as opposed to the maintenance T-SQL commands. The Database Maintenance Plan Wizard's point and click interface enables backups, DBCC's, UPDATE STATISTICS, etc. to be configured quickly in order to be executed on a regular schedule. So how is this accomplished in SQL Server 2005?

Database maintenance is vital to the health of an organization's data delivery infrastructure. In SQL Server 2005 Microsoft redesigned the method through which Database Maintenance Plans are created and managed. These plans can be created through a wizard in SQL Server Management Studio (SSMS). They are handled mainly through the Business Intelligence Studio (BIDS), and custom plans can be created in BIDS using SSIS. Although the process is comprehensive, the management of physical backup files is not automatically handled in the Database Maintenance Plan Wizard, but history cleanup is. A number of methods are available to manage these files.

One nice feature of SQL Server is the ability to create maintenance plans for all different types of maintenance activities such as backups, index rebuilds, integrity checks etc... One part of the backup process is the ability to create transaction log backups. One thing you may have noticed is that when you set this up and the job runs it always fails. You check the backup files and the backup files are created, so why does this job always say it failed?

As you have probably found out there are a lot of things that have changed with SQL Server 2005 and quite a few things that have remained the same. One of the changes that has occurred is how to setup maintenance plans in SQL Server 2000 vs. SQL Server 2005. This differences in setting up a maintenance plan were covered in this previous tip. Another change that has taken place is where the data about maintenance plans is stored. The old tables for maintenance plans still exists, but where is the data for the new plans?

Database maintenance is vital to the health of an organization's data delivery infrastructure. In SQL Server 2005 Microsoft redesigned the method through which Database Maintenance Plans are created and managed. These plans can be created through a wizard in SQL Server Management Studio (SSMS). They are handled mainly through the Business Intelligence Studio (BIDS), and custom plans can be created in BIDS using SSIS. Although the process is comprehensive, the management of physical backup files is not automatically handled in the Database Maintenance Plan Wizard, but history cleanup is. A number of methods are available to manage these files.

For better or for worse, the most common tool for performing database maintenance in SQL Server 2000 is the Database Maintenance Plan Wizard, as opposed to the maintenance T-SQL commands. The Database Maintenance Plan Wizard's point and click interface enables backups, DBCC's, UPDATE STATISTICS, etc. to be configured quickly in order to be executed on a regular schedule. So how is this accomplished in SQL Server 2005?

One nice feature of SQL Server is the ability to create maintenance plans for all different types of maintenance activities such as backups, index rebuilds, integrity checks etc... One part of the backup process is the ability to create transaction log backups. One thing you may have noticed is that when you set this up and the job runs it always fails. You check the backup files and the backup files are created, so why does this job always say it failed?

I have read the tip about securing SQL Server backups with a password. It is great option and I am planning to implement it for my production servers on SQL Server 2005 and SQL Server 2008. I am currenlty using a backup plan configured through maintenance plans, but I want to be able to create password protected backups. In this tip I will show you how this can be done.

As you have probably found out there are a lot of things that have changed with SQL Server 2005 and quite a few things that have remained the same. One of the changes that has occurred is how to setup maintenance plans in SQL Server 2000 vs. SQL Server 2005. This differences in setting up a maintenance plan were covered in this previous tip. Another change that has taken place is where the data about maintenance plans is stored. The old tables for maintenance plans still exists, but where is the data for the new plans?

I have changed the password of one of my SQL Server logins used for SQL Server Maintenance Plans. Everything was okay until a SQL Server Agent Job responsible for database backups failed. The SQL Server Agent Job was created by the Maintenance Plan Wizard. In this tip I will show you how to synchronize the password change in the SQL Server maintenance plan.

I have read the tip about securing SQL Server backups with a password. It is great option and I am planning to implement it for my production servers on SQL Server 2005 and SQL Server 2008. I am currenlty using a backup plan configured through maintenance plans, but I want to be able to create password protected backups. In this tip I will show you how this can be done.

I have changed the password of one of my SQL Server logins used for SQL Server Maintenance Plans. Everything was okay until a SQL Server Agent Job responsible for database backups failed. The SQL Server Agent Job was created by the Maintenance Plan Wizard. In this tip I will show you how to synchronize the password change in the SQL Server maintenance plan.

One nice feature of SQL Server is the ability to create maintenance plans for all different types of maintenance activities such as backups, index rebuilds, integrity checks etc... One part of the backup process is the ability to create transaction log backups. One thing you may have noticed is that when you set this up and the job runs it always fails. You check the backup files and the backup files are created, so why does this job always say it failed?

As you have probably found out there are a lot of things that have changed with SQL Server 2005 and quite a few things that have remained the same. One of the changes that has occurred is how to setup maintenance plans in SQL Server 2000 vs. SQL Server 2005. This differences in setting up a maintenance plan were covered in this previous tip. Another change that has taken place is where the data about maintenance plans is stored. The old tables for maintenance plans still exists, but where is the data for the new plans?

Database maintenance is vital to the health of an organization's data delivery infrastructure. In SQL Server 2005 Microsoft redesigned the method through which Database Maintenance Plans are created and managed. These plans can be created through a wizard in SQL Server Management Studio (SSMS). They are handled mainly through the Business Intelligence Studio (BIDS), and custom plans can be created in BIDS using SSIS. Although the process is comprehensive, the management of physical backup files is not automatically handled in the Database Maintenance Plan Wizard, but history cleanup is. A number of methods are available to manage these files.

For better or for worse, the most common tool for performing database maintenance in SQL Server 2000 is the Database Maintenance Plan Wizard, as opposed to the maintenance T-SQL commands. The Database Maintenance Plan Wizard's point and click interface enables backups, DBCC's, UPDATE STATISTICS, etc. to be configured quickly in order to be executed on a regular schedule. So how is this accomplished in SQL Server 2005?