SQL Server – How to List All Dropped Databases

sql serversql-server-2008sql-server-2012

I need to confirm if a particular table ever existed in our SQL Server. Is there an existing script or method one can use to list all dropped databases in an SQL Server instance?

Best Answer

Well, I don't know about ever, since SQL Server doesn't keep that information around forever.

Also, it's unclear if you are asking about tables or databases. Since you mentioned both, here is a query against the default trace (based on this question) that will return either, but only going back as far as the default trace goes. It shows who dropped what, when, and from where.

DECLARE @path nvarchar(260) = (SELECT 
    REVERSE(SUBSTRING(REVERSE([path]), 
    CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
  FROM sys.traces WHERE is_default = 1);

SELECT ObjectType,
       ObjectName, 
       DatabaseName, 
       LoginName, 
       HostName, 
       ApplicationName, 
       StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 47 AND EventSubClass = 1
  ORDER BY StartTime DESC;

The enum for ObjectType is documented here, but if you want to filter, you can use ObjectType = 16964 for databases and ObjectType = 8277 for tables.

The default trace rolls off and purges the oldest rows/files as it adds new ones. If you need to go back further than that, you will need to resort to restoring older backups, if you still have them.

Related Solutions

Sql-server – List all database containing table and value

I'm guessing on the first column - replace ColumnName below with the actual column name. Also it's always useful to specify which version of SQL Server you are using - I've assumed 2005.

DECLARE @DBType CHAR(1);
SET @DBType = 'A'; -- or 'B';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

CREATE TABLE #x(DB SYSNAME);

SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM '
  + QUOTENAME(name) + '.sys.tables WHERE name = ''DBType'')
  INSERT #x SELECT ''' + name + '''
    FROM ' + QUOTENAME(name) + '.dbo.DBType
    WHERE ColumnName = ''' + @DBType + ''';'
FROM sys.databases;

EXEC sp_executesql @sql;

SELECT DB FROM #x;

DROP TABLE #x;

SQL Server – Script All Databases to .sql Files with Schema, Data, and Users

I would personally just do a backup for each database, and then attach them to the new server. Just remember to use

EXEC sp_change_users_login 'Auto_Fix', 'username'

for fixing user accounts.

The Wizard approach sounds doable.

If you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier versions of SQL Server still works with 2008. The following will connect to a local SQL Server instance and create a DDL script for my_database:

scptxfr /s .\SQLExpress /d my_database /I /f database_script.sql /O /H

Without programming, it is going to be difficult to get all the data out in scripts.

Best Answer

Related Solutions

Sql-server – List all database containing table and value

SQL Server – Script All Databases to .sql Files with Schema, Data, and Users

Related Question