Accidentally change database name (SQL Server 2008)

Question

It didn't happen to me YET. But I was thinking about this.

I was messing around with my training environment, and by mistake I clicked in the database name, and then I touched the letter A.

Let's assume that I hit enter. Now the database is called A, and I don't remember the original name. CTRL + Z doesn't work.

What to do in a case like this, in a production environment?

I know it could not happen, because the database isn't set as SINGLE USER. But if it happens. What to do? For argument's sake, let's say it's a database that nobody is using right now.

Answer 1

Right click on the database, go to files. You can see the original file names. This will help you to find the correct database name easily. The file names won't be changed on a rename.

You can also try to take a look at your fn_dblog. It's undocumented but you can see (and filter) for the latest actions.

SELECT * 
FROM fn_dblog(NULL,NULL)

Answer 2

I doubt you would have a database in production whose name you do not know or not have documented it somewhere.

If incase it happens in production, You could look up list of existing backups using

RESTORE HEADERONLY FROM DISK = '<backuplocation>' 

Or use dbo.backupset from msdb.

SELECT DISTINCT database_name FROM msdb.dbo.backupset

Answer 3

You could look at the SQL Server Logs for the last time SQL Server started up and look out for each instance of 'Starting up database 'DBName'. You could then compare this list against the results of sys.databases. Any new databases and the one you changed will not be in the SQL Server log list.

Another, perhaps better way would be to query the default trace and filter by the ::fn_trace_gettable database_id. Assuming that there was some recent DB usage the databaseName column will show the old name and then in a more recent row the new name with an event type of Object:Altered.

SELECT  *
        ,cast(value as nvarchar(1000))
FROM   ::fn_trace_getinfo(default)
WHERE   traceid = 1 and   property = 2;

SELECT  ftg.StartTime,
        ftg.EndTime,
        te.name,
        ftg.sessionLoginName,
        ftg.ObjectName,
        ftg.DatabaseName,
        ftg.ServerName,
        ftg.LoginName,
        ftg.hostName,
        ftg.NTUserName,
        ftg.DatabaseID,
        ftg.TextData,
        ftg.TargetuserName
        ,SPACE(10) AS [Space]
        ,*
FROM    ::fn_trace_gettable('Z:\SQLServer\MSSQL\Log\log_5.trc', default) AS ftg 
INNER JOIN
        sys.trace_events AS te 
        ON  ftg.EventClass = te.trace_event_id  
ORDER BY
        ftg.StartTime DESC