April 20, 2009 at 5:23 am
how can i get the databases that are all dropped for last one week time
April 20, 2009 at 5:27 am
Backup!!?
"Don't limit your challenges, challenge your limits"
April 20, 2009 at 5:34 am
The only way is to take backup of all databases...........you can done it either weekly or daily.;-)..............
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
April 20, 2009 at 5:42 am
Get them back or know who dropped them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2009 at 5:48 am
I have to identify what are all the databases are dropped on last two weeks. I know i to get the databases that are created on last two weeks time. But don’t know how to get the databases that are dropped.
select * from sysdatabases WHERE crdate>='2009-04-06' AND NAME 'TEMPDB'
April 20, 2009 at 6:00 am
You can try the default trace. Depending on the activity on your system it may go back 2 weeks or it may not. The default trace is made up of 5 files of max 20 MB each. They're found in the same directory as the SQL error log. You should be able to open then in profiler or using the fn_trace_gettable function.
If this is going to be something you regularly need I would suggest a DDL trigger on DROP_DATABASE and a custom audit table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply