SQL Server: Database Maintenance Plan

Question

I have a small database for a school project and I need to create a Database Maintenance Plan with the following requirements:

• it has to manually (through scripting and not user interface)
• it must reorganize data and indexes
• it must validate database integrity
• it must be run automatically everyday at 19:00

well, I have absolutely no idea on how to do this

Can someone help me out, please?

I found some commands to create backups and reorganize indexes but I can't find a way to run them periodically.

Thanks Chiapa

Answer 1

If you want to create a maintenance plan, see the follwing page:

Use the Maintenance Plan Wizard

Otherwise, you can just create a SQL agent job with various steps in it that run the T-SQL commands that you want, such as backup database and DBCC commands and create a schedule for it.

If you cannot use the maintenance plan wizard, you can use commands similar to those detailed in the following page to create the jobs:

Create a Maintenance Plan

If you can't create agent jobs (for reasons such as you are using a version of SQL server that doesn't support them (SQL Express etc) then you can use OSQL commands that are fired by Windows task scheduler to achieve much the same thing, see the folowing link for a very good description:

How to Automate Maintenace Tasks with SQL Server Express

Answer 2

If you have the commands available and you're on an edition of SQL Server that has a SQL Agent, create an agent job and use that to do the scheduling.