October 26, 2004 at 3:49 pm
Is there a way to create a trigger for "on create database"? Ditto for "drop database". I want to add/remove backups automatically when databases are added/removed.
October 27, 2004 at 4:36 am
Yes it's possible.
Steps are as follows:
1. Create a trigger which is created to look after modofications of sysdatabases table of master database.
2. Since each dropping or creating of database will add or delete a row from this table you can search for the name of database being being deleted and search for backup in backup folder you have set and delete those backups.
Example : suppose I have set D:\backup folder for all databases and it will contain one folder with the name of each existing database.
Now suppose i am dropping a database named stores the u have to delete all backup files for this which will be in folder D:\backup\stores.
The command for deleting file is master..xp_cmdshell 'del d:\backup\stores'
Let me know if u stuck up somewhere implementing this
November 1, 2004 at 3:55 pm
It looks like I can't create a trigger on the sysdatabases table - is that correct?
November 2, 2004 at 12:55 am
Yes, you are right ... According to SQL Server BOL: "Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables".
Of course, you can try (NOT RECOMMENDED and NOT SUPPORTED!!!) and for some system table (I know at least two) your trigger will work ...
Better, take a look at this product/descrition:
<Lumigent Schema Alert>,
Introduction
Lumigent® Schema AlertTM is a utility for real-time detection of database changes. This utility tracks all activity of type CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE DATABASE, or DROP DATABASE on the selected SQL server. When one of these commands is detected, the Schema Alert utility generates an alert. These alerts appear in the Schema Alert window, and can also be placed in the Windows Event Log and/or sent to a specified email address.
http://www.lumigent.com/downloads/schemaalert/
Alexzander N. Nepomnjashiy
Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"
Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"
November 2, 2004 at 12:58 am
... also, you can post your SQL Server-related questions to this forum: http://myitforum.techtarget.com/forums/tt.asp?appid=73
Alexzander N. Nepomnjashiy
Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"
Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply