Transaction log back up

Question

Another accidental DBA question. We have a DB instance that host 10 databases. This DB server doesn't have any maintenance plan set up. I want to set up a decent maintenance plan that will do a full database back up everyday and do an hourly transaction log back up. I tried clicking on new maintenance plan button from SSMS and it looks like the SQL server agent is not configured. I plan to enable this by running the following

 sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 sp_configure 'Agent XPs', 1;
 GO
 RECONFIGURE
 GO

Then I plan to use the Maintainence plan wizard and set up my plan. Am I following the right route>? Do I have to watch for something while setting up the plan? Your advise is appreciated. Thanks

Answer 1

You can use Ola Hallengren's - SQL Server Maintenance Solution

This is much flexible than maintenance plans.

We have 300+ servers and we are using it for all our backups. It just works flawless.

To get you started :

• Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script

Answer 2

While the maintenance plans aren't the most flexable things in the world, for someone who is only managing just a couple of servers, maintenance plans are perfect for you. The code you show in your question will enable the SQL Agent, then you can configure and schedule your maintenance plans.

You'll also need to go into the "SQL Server Configuration Manager" and configure the SQL Agent service to start automatically, and then start it.

Answer 3

Simple answer: You are following correct path. You can create your own maintenance plan from MP wizard its easy and works fine. You don't need to use complex Ola Script.