| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2008-06-04 : 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
38200 Posts |
Posted - 2008-06-04 : 14:46:05
|
| No, but I'd be able to find out this information by looking at the last backup.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-04 : 14:58:14
|
| Do you mean to say - from msdb.dbo.backupset?------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-04 : 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-04 : 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
38200 Posts |
Posted - 2008-06-04 : 15:12:46
|
| 1. I don't understand the question.2. We just use sp_delete_backuphistory which takes care of everything for us. For 2000 though, I wrote a custom stored procedure for performance reasons, but in 2005 this condition no longer exists.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-04 : 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
38200 Posts |
Posted - 2008-06-04 : 15:22:02
|
| I don't understand why you wouldn't know which server the database was on. Couldn't you look in the connection string for the application that was pointed to it?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-04 : 15:29:27
|
| Let me put it this way. 1. There are many DBAs2. 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
38200 Posts |
Posted - 2008-06-04 : 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-04 : 15:38:31
|
| OK; Thanks for help.------------------------I think, therefore I am - Rene Descartes |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-04 : 15:42:57
|
| Or you can use DDL triggers to track information to reporting tables . |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-04 : 15:48:58
|
| That is a good suggestion. Thanks.------------------------I think, therefore I am - Rene Descartes |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-04 : 15:53:43
|
| This is really good way to track who did it and at what time. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-04 : 15:56:45
|
| I agree...------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
DB deletion time