| Author |
 Topic  |
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
 Posted - 06/04/2008 : 14:28:31
|
Is there an option to find out the deleted DBs on a server?
------------------------
I think, therefore I am - Rene Descartes
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 06/04/2008 : 14:58:14
|
Do you mean to say - from msdb.dbo.backupset?
------------------------
I think, therefore I am - Rene Descartes
|
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 06/04/2008 : 15:01:43
|
No as we purge the msdb backup history. I'd look in our Backup\dbName folder and check the date of the last transaction log backup. We back them up every 15 minutes, so if a database got dropped, we could assume the time of the drop by looking at the last tlog backup for that database. We'd be within 15 minutes of being accurate.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 06/04/2008 : 15:10:39
|
1. How do you find out which server to look for?
2. What tables do you purge from MSDB?
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 06/04/2008 : 15:19:06
|
1. I have around 50 servers. According to your suggestion i have to look in all these servers for the deleted db. This is a tedious thing.
2. thanks.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 06/04/2008 : 15:29:27
|
Let me put it this way.
1. There are many DBAs
2. Applications have a very short life time (2-3 months)
3. every month lot of DBs get created and many gets deleted.
4. I need to give a report to the management.
Hope this helps.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 06/04/2008 : 15:34:21
|
If I were you, I'd write a program to collect this data. The program could simply be a batch file that calls osql/sqlcmd and puts the results of sysdatabases/sys.databases into a report table. Schedule the program to run daily and run T-SQL against the report table to get the info management wants.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 06/04/2008 : 15:38:31
|
OK; Thanks for help.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 06/04/2008 : 15:42:57
|
| Or you can use DDL triggers to track information to reporting tables . |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 06/04/2008 : 15:48:58
|
That is a good suggestion. Thanks.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 06/04/2008 : 15:53:43
|
| This is really good way to track who did it and at what time. |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 06/04/2008 : 15:56:45
|
I agree...
------------------------
I think, therefore I am - Rene Descartes
|
|
|
| |
Topic |
|
DB deletion time
