Backup sql2005 using maintenance plan

I am new to Sqlserver 2005 and I have created the maintenance plan where about 10 databases are being backedup using the maintenance plan described in the following link: http://www.mssqltips.com/tip.asp?tip=1094 However I have not used all the steps described in the plan as I am not sure what would be the significance of each plan. In my plan I have Back Up Database Task and Maintenance Cleanup Task. What are the other tasks I need to include in the maintenance plan? If anyone can send me some advice that would be a big help.



more ▼

asked Nov 30, 2009 at 04:18 PM in Default

Chitra gravatar image

148 15 17 19

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you have your databases in full recovery mode, you need to include log backups as well. Otherwise your logs will grow constantly. You can read more about this here: http://www.sqlservercentral.com/articles/64582/

You probably want to clean up the history at some point in time. This removes the logs of the maintenance plan. I'd probably keep a month around and remove them on a rolling basis.

As far as maintenance, the reindex task is good to run periodically. Maybe weekly if you don't understand what it does. once you gain more experience, you might think about using your own reindex procedure that is a little smarter and running that weekly as well. We have a few scripts here: http://www.sqlservercentral.com/Scripts/Index+Management/

more ▼

answered Nov 30, 2009 at 04:41 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

Good advice. Generally, (this is simplifying) a production database should be in full recovery with log backups at least daily possibly, much more often depending on circumstances. Generally, test databases can be left in simple logging.
Nov 30, 2009 at 08:02 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 30, 2009 at 04:18 PM

Seen: 1225 times

Last Updated: Nov 30, 2009 at 08:02 PM