Unanswered: Trigger procedure execution on database creation.
Is there a way to trigger the execution of a procedure when a database is created ? We're using this 3rd party system in which you only specify the database server. Whenever the users create a new "Storage Area", the system actually creates a new database.
I need to handle backups for that system so I want to create a procedure that will generate a backup device and backup jobs automatically whenever a database is created.
I will also need 2 procedures to handle database renaming and deletion. I searched around but can't find a way to trigger the execution of my procedures.
Create a stored procedure in some "majik" database of your choosing (I'd recommend against using master in most cases). Execute that procedure to create your database, backup device, and associated jobs. You can put your rename and delete procedures in the same place.
The problem is that I don't have any control on database creation. The system we're using work with what they call a "Storage Area" which has to be created by the system itself. If I just create a database with the necessary tables it won't recognize it as a "Storage Area". The problem is that I have to let them create "Storage Areas" as they need. This is why I'm trying to see if I can somehow trigger the execution of a procedure when a database is created.
I don't know of a way to trigger on database creation, but there's nothing that I know that would prevent you from running a stored procedure as the first stop of your backup job that would add new devices, and queue the new databases for backup. FYI, I usually skip the backup devices entirely and just have a stored procedure that backs up the databases listed in a table (often master.dbo.sysdatabases!) into files with the same name as the database. It works well for me.
In Enterprise Manager, create two maintenance plans:
AllDatabases: Check the "All Databases" option on the first tab.
UserDatabases: Check the "User Databases" option on the first tab.
Do not have these maintenance plans actually do any maintenance! They are for naming purposes only.
Now, create a log backup job that applies to all user databases. The command will be:
EXECUTE master.dbo.xp_sqlmaint N'-PlanName UserDatabases -Rpt [YourReportPath] -DelTxtRpt 5Days -WriteHistory -BkUpLog [YourBackupLogPath] -BkUpMedia DISK -DelBkUps 5Days -CrBkSubDir'
Then create a database backup job the applies to all databases. The command will be:
EXECUTE xp_sqlmaint N'-PlanName AllDatabases -Rpt [YourReportPath] -DelTxtRpt 5Days -WriteHistory -BkUpDB [YourBackupDatabasePath] -BkUpMedia DISK -DelBkUps 5Days -CrBkSubDir'
Finally, set the recovery option of all existing databases to FULL RECOVERY, and set the option for MODEL as well so that all new databases will use FULL RECOVERY.
Voila! Any new database created is automagically included in the backup and log cycle, and gets its own backup directory created in the backup path.
So lazy, ya gotta love it.
If it's not practically useful, then it's practically useless.