Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Narendran4it
Starting Member
1 Post |
 Posted - 2012-02-10 : 02:10:28
|
| Hi Friends, Can You Please Help me out from it, When ever delete database or drop command is fired in sql server , It should automatically take a backup with out knowing the Front End User.Narendran |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-10 : 06:57:29
|
| I have never tried this, but what you probably need is a DDL trigger at the server level. http://msdn.microsoft.com/en-us/library/ms186406.aspxHowever, I would be VERY weary about this design. In my mind at least, databases are sort of "permanent objects" that you don't drop or create in response to normal routine user actions.If you describe the business problem you are trying to solve, some of the experts on this forum may be able to suggest better and simpler alternatives. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-10 : 07:40:10
|
| I'm guessing, but if the user has the ability to drop a database (and this is the cause of the issue) then either:provide the user with an option to take a backup, and remind them that they should do this before the DROP the database!!whatever code is run server-side when the user does a "Drop database" from their front-end, then incorporate a backup into the Procedure. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-10 : 15:56:23
|
| That's silly. Just take regular backups -- including log backups.If databases are getting dropped, someone's admin access needs to be removed. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-10 : 17:36:20
|
quote:
Originally posted by russell
If databases are getting dropped, someone's admin access needs to be removed.
Web based bureau system, user "requests" a new site, database created etc etc and user can then request the site to be deleted, but a final backup should be taken before actually dropping it in case user presses "UNDO" !!. Seems a reasonable scenario to me. However I have no idea what the O/P's business model is! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-10 : 17:53:22
|
quote:
Originally posted by Kristen
quote:
Originally posted by russell
If databases are getting dropped, someone's admin access needs to be removed.
Web based bureau system, user "requests" a new site, database created etc etc and user can then request the site to be deleted, but a final backup should be taken before actually dropping it in case user presses "UNDO" !!. Seems a reasonable scenario to me. However I have no idea what the O/P's business model is!
I don't know Kristen, on this I am with russell. In the scenario you described, I would mark the database for deletion in some controlling database, and have my own process doing the "garbage collection" of databases to be deleted.I can't think of any robust scenario where you would want the client to drop a database. Then again, I have been a database programmer only for a short period of time, so I can be convinced otherwise. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-02-10 : 18:04:42
|
| I also agree with russell, take regular backups. If you can't revoke their permission to drop, DON'T take a backup before dropping. (It's going to be very difficult to do anyway)If they want that permission, they assume the responsibility and consequences. |
|
|
|
|
|
|
|
|
Backup Trick