SQL Server 2005 Backups - Best Practices 1

[4MULA1]

Need Assistance

Question 1
SQL 2005 Enterprise Server SP2. I can not backup all the databases at one time. Using the Maintenance Plan Wizard and I can backup all the user databases at one time but once I select all the database it gives me an error on the Transaction Log step
Cannot back up the log of the master database. Use BACKUP DATABASE instead.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am able to backup the system databases at one time on a seperate maintenance plan without using the Tranactional backup.

I would like to use one maintenance plan that allows for both Full backups (one per day) and Transactional backups (every 2 hours). Is this possible?

 

[mrdenny]

The system databases are setup in simple recovery so transaction log backups don't work for them.

It's normal for most people to setup two maintenance plans. One for user databases as they need to be backed up often, and one for system databases which don't need to be backed up very often as they typically have little to no change from day to day.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[4MULA1]

Thanks

Another Question

I have inherited another SQL Server 2005 Server and have very little experience with SQL

Weekly maintenance plan

Check Database Integrity
Backup database Tranaction Logs
Shrink Database
Backup Database Transaction Logs
Reorganize Index
Backup database Transaction Logs
Rebuild Index
Backup Database Transactions Logs
Update statistics
Backup Database Tranactions Logs
Clean Up History

Is this the proper order and why are the Tranactions logs backup up after every other step?

Thanks for any help.

 

[mrdenny]

The logs are backed up after each step because each of those steps will generate quite a log of log data. Who ever wrote the plan is trying to keep the logs as small as possible.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog