question

Chitra avatar image
Chitra asked

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.

Regards

Chitra

backupmaintenance
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Steve Jones - Editor avatar image
Steve Jones - Editor answered

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/

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.