8

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.

  • 2
    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 – Mark Sinkinson Jun 19 '15 at 11:53
11

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)
  • 2
    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 – Martin Smith Jun 20 '15 at 20:50
7

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
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.