• The name "Maintenance Plan" pretty much defines what you're going to be building. A set of routines for maintaining your database and server. This is not the same thing as preparing a disaster recovery scenario or implementing any of the various means of high availability. So the request you were given is at odds with what's possible.

    That said, as far as maintaining the system goes, you should schedule regular consitency checks and backups. How often is dependent on the system, the size of the databases in question, etc. For example, my team maintains somewhere in the neighborhood of a 1,000 plus databases on about 25 production servers. None of the databases are very large (biggest is around 250gb I think). We run DBCC checks nightly because it's effectively cost free. We also run nightly backups (after the DBCC checks, so we only back up databases that are consistent). In addition, we have, on most databases, but not all, regularly scheduled log backups. This will help us reduce the possibility of data loss (but not eliminate it as your admin has suggested). Again, here, you can vary the schedule. We have some instances that are running every 10 minutes, some every 15 and some every 30. It just depends on the system and the data needs.

    In addition to all this, we also have routines that defrag/rebuild indexes based on the level of fragmentation and number of pages in the index. This runs daily (again, because our systems are small). We also update statistics daily, sampled. There are a couple of systems where we have a statistics update job that runs every 30 minutes on a particular table or set of tables. Once more, this completely depends on the needs of your system.

    All this will help prevent data loss. Without a full set of DR and HA systems in support of these maintenance routines, you cannot get to a point where you effectively eliminate data loss.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning