Post reply

How to write trigger on server level to take backups of databases before it is deleted.

  • November 4, 2008 at 2:46 am

    #195588

    Hi All,

    I got one task related to database backups, it is like

    When user tried to Delete a database in the server.

    1) log the user id, DateTime in some Logtable.

    2) Take backup of the database before it is deleted.

    I know SQL Server have 'DDL Triggers' at server level and database level, I don't know how to use this related to the task given.

    If any one know about this please help me.

    Thanks in advance

    Mobile Application Development[/url]

  • November 4, 2008 at 4:48 am

    #893528

    You can create DDL trigger as

    CREATE TRIGGER ddlTrigger

    ON ALL SERVER

    FOR DDL_LOGIN_EVENTS

    AS

    .....

    ..

    …

    .

    You can use EVENTDATA() function that returns info such as event time, SPID, and type of event firing the trigger in form of XML data.

    DBDigger Microsoft Data Platform Consultancy.

  • November 4, 2008 at 5:15 am

    #893543

    Thank you

    Atif

    Can you explain how we can take backup of the deleted database in DDL triggers.

    Mobile Application Development[/url]

  • November 5, 2008 at 1:08 am

    #894080

    Look for use of 'INSTEAD OF Triggers'. As triggers are of two types after and instead of. in your case 'instead of trigger' may work.

    DBDigger Microsoft Data Platform Consultancy.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply