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 |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2008-10-21 : 06:05:12
|
Hi allI've got a theoretical question to ask about recovering a database following disaster. Let's take the following example:I have a frequently used sql 2005 database that has the following backup schedule:- Weekly Full
- Daily Differential
- Hourly Transaction Log
Let's suppose that at some point somebody gets through the security mechanisms and truncates a huge amount of data from the database. Generally, how do admin's know which point to restore the database to? Is there some way to determine when the data was deleted?Also, how do you keep any data entered into the database since the point where data was lost?Thanks in advance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-21 : 09:52:34
|
| Thats why security is important aspect if data in your company is very crucial.You shouldn't give unnecessary rights to people.For ex:Alter table permission can let user truncate whole table.You should use DDL trigger and DML to track it down.Since you are also doing hourly,you can trackit down from there too. |
 |
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2008-10-21 : 10:08:13
|
| HiMy security is extremely tight, since its an ASP.NET app and the only way to access the server using SQL is via VPN from a fixed IP.Only my SA account can 'see' anything beyond executing simple stored procedures, and again only from my fixed location.So its really theoretical for my own interest. Can you explain what you mean by DML and DDL please? Is there a way to see what time certain commands executed? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-21 : 10:18:31
|
| Search for DDL trigger to track system date and who did it. |
|
|
|
|
|
|
Question about recovering following a disaster