I like your idea Steve.

It seems feasible for there to be a config setting or switch to make this functionality optional (i.e. ContainedDatabaseAdminFlag ) as to mandatory for all databases on the instance.

I have seen a home-grown attempt at this concept, and I think it wasn't implemented very well. First discovered in a review of existing servers, I noticed there was not a single SQL Agent Job. How could this be? I saw backup files so knew it was being done (at least sometimes without error). The developers included stored procedures to backup, reindex, archive etc. into the user database for the application. Unfortunately, they didn't include a user-interface in the application to configure them. It turns out that they had Windows Scheduler Tasks to execute these procedures.

What followed was more than a bit of a mess. Hard-coded backup paths and schedules. Any change to the setup was one-off, and we had several hundred servers to manage on client sites. I fully understand their intention, but also understand some fundamental flaws in design and implementation.

For example, new storage was installed to replace failed disks and was not designated the previous drive letter. Transaction log backups failed, transaction log filled, database became unavailable, no backups at all. No one to review ERRORLOGS, no DBA, no front-end interface to notify users. A database calling it's own backup? Just sounded like a terrible idea to me, for some of the same reasons stated by other responders.

In the end, I went back to the source code and had them remove all of that "garbage"... I was truly offended that the developers wouldn't rely on a super-smart seasoned professional DBA like me to manage the instances! So then, the smart guy gets to run with his smart idea. Strip out theirs, and configure backups and maintenance tasks in SQL jobs that any DBA could figure out. Now I have some backups on some instances managed by their own database, some managed on the instance level, and two versions of the deployment package, at least until we get this mess straightened out.

Fast forward two weeks. One of the instances I had "fixed" had full disks (oh, I forgot to mention that data .mdfs, log .ldfs, and backup .baks all resided on the same physical drive... again a hard-coded feature of the software, that basically required the client to buy a standalone instance of SQL) The disk was full because not only were my backups working, but the internal user database backups were working as well. Support Department saw the version of the database, ran a deploy/repair utility that reinstalled all of the components I had stripped.

No matter features or the technology used, the whole organization, including the end client, need to understand how the pieces work together. Is it right to have a user database backup itself? Is it right to have developers manage with internal code? Should the DBA be in charge of maintenance and administration? As far as and end-user and client is concerned, it doesn't matter. What matters is availability, recoverability, stability, consistency, and maintainability. Our downfall came undoubtedly from lack of communication.

Egos and pride got in the way of the end goal. I shot myself in the foot!

Ouch.