SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 DB deletion time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  14:28:31  Show Profile  Reply with Quote
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
37142 Posts

Posted - 06/04/2008 :  14:46:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
No, but I'd be able to find out this information by looking at the last backup.

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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  14:58:14  Show Profile  Reply with Quote
Do you mean to say - from msdb.dbo.backupset?

------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 06/04/2008 :  15:01:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  15:10:39  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 06/04/2008 :  15:12:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  15:19:06  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 06/04/2008 :  15:22:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  15:29:27  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 06/04/2008 :  15:34:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  15:38:31  Show Profile  Reply with Quote
OK; Thanks for help.

------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/04/2008 :  15:42:57  Show Profile  Reply with Quote
Or you can use DDL triggers to track information to reporting tables .
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  15:48:58  Show Profile  Reply with Quote
That is a good suggestion. Thanks.

------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/04/2008 :  15:53:43  Show Profile  Reply with Quote
This is really good way to track who did it and at what time.
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 06/04/2008 :  15:56:45  Show Profile  Reply with Quote
I agree...

------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000