SQL SERVER – Recover the Accidentally Renamed Table

I have no answer to following question. I saw a desperate email marked as urgent delivered in my mailbox.

I accidentally renamed table in my SSMS. I was scrolling very fast and I made mistakes. It was either because I double clicked or clicked on F2 (shortcut key for renaming). However, I have made the mistake and now I have no idea how to fix this.

I am in big trouble. Help me get my original tablename.”

I have seen many similar scenarios in my life and they give me a very good opportunity to preach wisdom but when the house is burning, we cannot talk about how we should have conserved the water earlier. The goal at that point is to put off the fire as fast as we can. I decided to answer this email with my best knowledge.

If you have renamed the table, I think you pretty much is out of luck. Here are few things which you can do which can give you idea about what your tablename can be if you are lucky.

Method 1: (Not Recommended but try your luck)
Check your naming convention of your system. I have often seen that many organizations name their index as IX_TableName_Colms or name their keys as FK_TableName1_TableName2_Cols. If your organization is following the same you can get the name from your table, you may refer your keys. Again, note that this is quite possible that your tablename was already renamed and your keys were not updated. This can easily lead you to select incorrect name. I think follow this if you are confident or move to the next method.

Method 2: (Not Recommended but try your luck)
This method is also based on your orgs naming convention. If you use the name of the table in any columnname (some organizations use tablename in their incremental identity column name), you can get that name from there.

Method 3: (Not Recommended but try your luck)
If you know where your table was used in your stored procedures, you can script your stored procedure and find the name of the table back.

Method 4: (Try your luck)
All the best organizations first create a data model of the schema and there is good chance that this table is used there, you should take your chances and refer original document. If your organization is good at managing docs or source code, you will get the name of the table back for sure.

Method 5: (It WORKS but try on a development server)
There is no sure way to get you the name of the table which you accidentally renamed however, there is one way which will work for sure. You need to take your latest full backup and restore it on your development server (remember not on production or where you have renamed this column). Now restore latest differential file of the full backup. Now restore all the log files one by one making sure that you are restoring before the point of time of you renamed the tablename. Now go to explore and this will give you the name of the table which you have renamed. If you are confident that the same table existed with the same name when the last full backup was made, you do not have to go to all the steps. You can just get the name of the table directly from last backup’s restore. Read the article about Backup Timeline.

Method 6: Interesting Solution by Muhammad Imran
I suggest you to go ahead and read it yourself.

Wisdom:
How can I miss to preach wisdom when I get the opportunity to do so? Here are a few points to remember.

  • Use a different account to explore production environment. Do not use the same account which have all the rights and permissions all the time. Use the account which has read only permissions if there are no modification required.
  • Use policy based management to prevent changes which are accidental. If there was policy of valid names, the accidental change of the table was not possible unless it was intentional delibarate changes.
  • Have a proper auditing of the system in place.
  • You can use DDL triggers but be careful with its usage (get it reviewed properly first).
  • (Add your suggestion here)

I guess Method 5 will work all the time (using point in time restore). Everything else is chance of luck and if you are lucky are bad – you will get further incorrect name.

Now go back and read the first line of this blog. Out of five method four methods are just lucky guesses. The method 5 will work but again it is a lengthy process if the size of the database is huge or if you do not have full backup. Did I miss anything obvious? Please leave a comment and I will publish your answer with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

16 thoughts on “SQL SERVER – Recover the Accidentally Renamed Table

  1. If we have latest (full + differential + log) backup, restore it to some different instance (or on same instance with different database name) and do the schema comparision. This will give the difference of table name.

    Like

  2. It can also be recoverd from the SQL Server log.

    For Example :

    SELECT [RowLog Contents 0] FROM sys.fn_dblog(NULL, NULL)
    WHERE Context IN (‘LCX_MARK_AS_GHOST’,’LCX_INDEX_LEAF’)
    AND Operation in (‘LOP_DELETE_ROWS’,’LOP_INSERT_ROWS’)
    And AllocUnitId IN (562949955649536)

    And [RowLog Contents 0] need to be decoded and get the before and after renamed table name.

    Here is the solution :

    http://raresql.com/2012/10/08/sql-server-how-to-recover-the-renamed-table/

    Like

  3. Another option similar to 5 would be to use the Red-Gate tool Sql Compare. Sql Compare can use a backup file as the source. The compare will tell you the differences with out having to actually do the restore. Depending on the size of the database, it will take a while to read thru the backup file to pull out the schema.

    Like

  4. Use a script like this to list all the tables in the schema then look for the ‘missing’ table:

    SELECT
    SUBSTRING(o.name,1,50) AS TableName
    FROM
    sysobjects o
    ORDER BY
    1 ASC

    Like

  5. Try your application that works with the database. It might give you an error like ‘table … doesn’t exists’ in the logfiles.

    Like

  6. This can easily identified if we have followed standard in our coding.
    For example, if we have created INDEX with naming convention like ‘IX_TableName_Colms ‘ from here we can easily get the name of the Table before change.

    Regards,
    Girijesh

    Like

  7. +1 to Imran’s method. You might also be able to recover it from the system_health session. Finally, run something like Event Notifications.

    Like

  8. There is another method that I use on all my production instances. A DDL trigger fires whenever a DDL action is performed, in this case the renaming of a table. The results gathered by the trigger are stored in a table that logs the statement including the table name before and after.

    This technique works whether a SQL statement is physically typed in or performed in the SSMS with a right mouse click.

    The only other solution is to wait until a user tries to do something and fails. Look at the query and figure out which part is broken, your number 3 I believe.

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s