How can I find the Database ID of a deleted database?

Question

Where can I view/find the database ID after a database was deleted?

I work in software/hardware environment and we have locking unit (prototype) synced in our S/W with unique DB ID. In order to reset this locking unut we need to know the DB ID. Unfortunately DB was completely deleted and we have no way to continue our work.

Database was deleted about 6-7 days ago.

Answer 1

You could inspect the default trace to see the database id for a deleted database, assuming the database was deleted recently enough for the drop to be captured in the trace.

This T-SQL will show you the trace records:

DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path 
FROM sys.traces 
WHERE is_default = 1;

IF (SELECT COALESCE(OBJECT_ID('tempdb..#trctemp'), 0)) <> 0
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt

SELECT tt.DatabaseID
    , tt.DatabaseName
    , tt.StartTime
    , tt.HostName
    , tt.LoginName
    , tt.ApplicationName
FROM #trctemp tt
WHERE tt.eventclass = 47 
    AND tt.objecttype = 16964
ORDER BY tt.StartTime DESC;

Answer 2

Depending on how long you keep your logs for and how long it has been since the MSSQLSERVER or MSSQL$instance_name was (re)started, you might be able to find this info listed in the SQL Server Log. Here are two ways to look for it:

1. In SQL Server Management Studio (SSMS):

   1. Go to Object Explorer
   2. Connect to the server that had the Database dropped from it
   3. Go to the Management folder
   4. Go to SQL Server Logs
   5. Double-click on Current
   6. On the right side, in the top button bar, click "Filter..."
   7. In the "General" section, in the text field for "Message contains text", enter in the following:
      recovery
   8. Check the "Apply filter" check-box
   9. scroll through the results (you might need to scroll the right-side over to the right so you can see everything. You should see entries similar to:
      • Recovery is writing a checkpoint in database 'master' (1).
      • Recovery completed for database mssqlsystemresource (database ID 32767) in 1 second(s)
      • Recovery of database 'Test' (13) is 2% complete (approximately 49 seconds remain).
   10. If you don't see the database that was dropped, you might need to check additional / older logs on the left side of the "Log File Viewer", where the title is "Select logs".
       
       

2. In Windows Explorer:

   1. Go to the following folder:
      C:\Program Files\Microsoft SQL Server\MSSQLvv.InstanceName\MSSQL\Log\
      where vv is the SQL Server version number (e.g. 11 = SQL Server 2012, 12 = SQL Server 2014, etc).
   2. Search the following file(s) for the text "recovery":
      ERRORLOG[.XX]
      where the [.XX] is the optional log sequence number and the current log file has no sequence number.