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.

Case

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.

case2

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.

• Mark Sinkinson: I'd have a specific user that has ALTER DATABASE permissions and only log in as that user when I actually want to run an ALTER DATABASE script. That way you'll never do the above

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)

• Martin Smith: The data isn't displayed from this query in plain text. Would need to cast it to readable form with something like SELECT CAST([RowLog Contents 0] AS sysname) ,CAST([RowLog Contents 1] AS sysname) FROM sys.fn_dblog(NULL,NULL) WHERE Context = 'LCX_BOOT_PAGE' AND [Offset in Row] =52

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