DDL trigger for RESTORE DATABSE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » DDL trigger for RESTORE DATABSE

17 posts, Page 1 of 2

1 2 »»

DDL trigger for RESTORE DATABSE

Rate Topic

Display Mode

Topic Options

Author

Message

Ajay Prakash

Posted Sunday, February 17, 2008 4:04 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 53, Visits: 182

Hi,

I have to create a user in each user database after a database has been created or restored in SQL Server 2005. Not able to do so with a DDL trigger for restore operation. Any solution?

Regards,
Ajay Prakash

Kindest Regards,

Ajay Prakash

Post #456766

michaela

Posted Sunday, February 17, 2008 8:54 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:21 PM
Points: 1,389, Visits: 417

unlike create database, restore is not a ddl statement with server scope; read BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a3d3c1a5-6ca0-465b-b1d4-f197dd2b682d.htm

Post #456772

lrosales

Posted Monday, February 18, 2008 10:33 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:00 PM
Points: 77, Visits: 399

I do not understand the question. are you attenpting to create a database(or restore the db) and then create the user with a triggered?
am correct?

"We never plan to Fail, We just fail to plan":)

Post #456991

Ajay Prakash

Posted Monday, February 18, 2008 5:44 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 53, Visits: 182

The new user has to be created after any of the below mentioned operation happens.
1. If a new database has been created
OR
2. If a any database has been retored.

Kindest Regards,

Ajay Prakash

Post #457135

thecosmictrickster@gmail.com

Posted Monday, February 18, 2008 6:33 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 06, 2013 8:37 PM
Points: 2,609, Visits: 768

Presumably the database being restored is from another server? Otherwise your user would already exist if you were restoring a database that was already present on your server (assuming you have retroactively created this user on all your existing databases).

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Post #457151

Ajay Prakash

Posted Tuesday, February 19, 2008 10:19 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 53, Visits: 182

Scott,

I work in an environment where my team is supporting almost 500 server with all sorts of environment(DEV/UAT/SIT/PROD/DR). These systems are being used by multiple applications, which often requires database restoration from PROD no NON PROD or from NON PROD as well NON PROD. On the top of this application team may tell us to restore databases of 2 years back. I may rollover the users accross the servers once but this will not suffice my requirement. We want to track object creation in any user database and a report goes to each application manager/owner next day. EAch server is capable of sending report on its own.

Therefore I was thinking to have a DDL trigger at each user database level and a server level DDL trigger to cater create and restore database.

Even SQLAgent job does not address my concern accurately becaue the objects can be created just after a restore happnes which will not be rerported. We have a central repository on each server where all database reports it's object creation

Regards,
Ajay Prakash

Kindest Regards,

Ajay Prakash

Post #457508

thecosmictrickster@gmail.com

Posted Tuesday, February 19, 2008 11:44 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 06, 2013 8:37 PM
Points: 2,609, Visits: 768

In lieu of a trigger, have you considered scripting the restore process, including post-restore steps of creating the user you require? If it is a regular occurrence, maybe implement as a stored procedure, passing db name, path to data files, path to backup file.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Post #457558

Ajay Prakash

Posted Wednesday, February 20, 2008 10:09 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 53, Visits: 182

Thanks!! I think this may be a good solution to it. Will try not sure if that will be feasible for all to follow. But I will try my best..

Kindest Regards,

Ajay Prakash

Post #458098

Sean Elliott (UK)

Posted Wednesday, July 07, 2010 12:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42, Visits: 170

See http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx for a full solution including auditing of RESTORE DATABASE and CREATE DATABASE FOR ATTACH in SQL 2005 (and SQL 2008)

Post #948332

Dirk.Hondong

Posted Tuesday, July 27, 2010 1:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 4:40 AM
Points: 95, Visits: 1,087

SecretSQuireL (7/7/2010)

See http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx for a full solution including auditing of RESTORE DATABASE and CREATE DATABASE FOR ATTACH in SQL 2005 (and SQL 2008)

It seems that this forum entry does not exist. Or is it the wrong url?

Regards
Dirk

--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter

Post #959219

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions