SQL Server 2008 – How to Handle Accidental Database Name Change

sql serversql-server-2008

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.

Best Answer

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)

Related Solutions

Sql-server – Change SQL Server 2008 name

If you aren't changing the physical server's name you'll need to change the name that SQL Server knows back.

sp_dropserver 'SERVER2\SQLSERVER'
sp_addserver 'SERVER1\SQLSERVER', local

You then just need to put an alias in DNS so that when people try to connect to SERVER2 then are redirected to SERVER1 without knowing about it. Specifically you'll want (or you'll want your sysadmin) to put a CNAME record into DNS pointing SERVER2 to SERVER1.

Sql-server – Missing PK/FK after database migration

Script out the constraints from the staging database and apply to the problem database. Lots of options, just make sure you have backups of staging and prod before you start:

Manually, one by one. Right click the objects in SSMS and opting to 'Script'.
Manually, all together (with a bit of editing). Right click database in SSMS and Tasks / Generate Scripts / Select 'Tables'. Under Scripting Options / Advanced include PK/FK. The script created will include table definitions, you'll have to edit to leave just PK/FK.
Use a schema comparison tool to sync staging to production.
Use a script to generate the ALTER statements for you.


    SELECT
        *
        , 'ALTER TABLE ['+ FKTableSchema +'].[' + FKTableName + '] DROP CONSTRAINT ' 
            + ForeignKeyName AS FKDrop
        , 'ALTER TABLE ['+ FKTableSchema +'].[' +FKTableName + '] ADD CONSTRAINT ' 
            + ForeignKeyName + ' FOREIGN KEY(' +
          FKColumnList +') REFERENCES ['+ PKTableSchema+'].['
            + PKTableName + '] ('+PKColumnList +')' AS FKCreate
        , 'ALTER TABLE ['+ PKTableSchema +'].[' +PKTableName + '] DROP CONSTRAINT ' 
            + PrimaryKeyName AS PKDrop
        , 'ALTER TABLE ['+PKTableSchema +'].[' +PKTableName + '] ADD CONSTRAINT ' 
            + PrimaryKeyName + ' PRIMARY KEY('+PKColumnList+')' AS PKCreate
    FROM
        (
        SELECT DISTINCT
            FKTableSchema = 
                (
                SELECT DISTINCT 
                    table_schema 
                FROM 
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE 
                    constraint_name=rc.constraint_Name
                ),
            FKTableName = 
                (
                SELECT DISTINCT 
                    table_Name 
                FROM 
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE 
                    constraint_name=rc.constraint_Name
                    ),
            rc.constraint_name AS ForeignKeyName,
            FKColumnList = 
                (
                SELECT 
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList' 
                FROM
                    (
                    SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE constraint_name=rc.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                ),
            cu.table_schema AS PKTableSchema,
            cu.table_name AS PKTableName,
            cu.constraint_Name AS PrimaryKeyName,
            PKColumnList = 
                (
                SELECT 
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList' 
                FROM
                    (
                    SELECT 
                        Column_Name + ',' 
                    FROM 
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE 
                        constraint_name=cu.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                )
        FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
        INNER JOIN
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
        ON  rc.Unique_Constraint_name= cu.Constraint_name
    ) AS tab

Best Answer

Related Solutions

Sql-server – Change SQL Server 2008 name

Sql-server – Missing PK/FK after database migration

Related Question