0

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

chiapa
  • 4,362
  • 11
  • 66
  • 106
  • 1
    What type of database is it? You need to search the internet for the tasks you want to do. I doubt anyone here will actually do your school project for you if you try and do somethings and then get stuck peeps on here can help out but essentially how can you learn if something is done for you – Dev N00B Jun 26 '13 at 15:56
  • It's not a school project, it's only one of the requirements. I don't want people to do it for me, but show me how to. I found some commands to backup the database and reorganize indexes but it 's useless if I can't put it in some kind of structure (like a stored procedure or something) that runs periodically, everyday at a certain time – chiapa Jun 26 '13 at 16:00
  • Are you saying you need to create the Database Maintenance Plan manually, or can you use the UI to set that up? – rsbarro Jun 26 '13 at 17:05
  • I need to create it manually. I can't use UI tools. – chiapa Jun 26 '13 at 17:39

2 Answers2

2

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

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • Hi, thanks for your answer but I haven't got a SQL Server Maintenance Plan Wizard. I saw that was a possibility but I'm not allowed to use it (even if I had it) so I must write the commands and have them running automatically at a specific time periodically without it – chiapa Jun 26 '13 at 16:36
  • 1
    Please see my edit that has more detail on what you can do if you can't use the maintenance plan wizard, you should be able to find something there that works for you. – steoleary Jun 26 '13 at 18:58
1

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.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68