Revert backup table data to original table SQL

Instead of dropping the table, which, as you noted, would lose all the permission defitions, you could truncate it to just remove all the data, and then insert-select the old data:

TRUNCATE TABLE country;INSERT INTO country SELECT * FROM county_bkp;

Only One Solution to Recover Data from Backup table is Rename Original table with random name and than rename Backup table with Original Table name in case if Identity Insert is ON for Original Table.
for example
Original Table - Invoice
Back Up Table - Invoice_back

Now Rename these tables :

Original Table - Invoice_xxx
Back Up Table - Invoice

In my case, INSERT INTO country SELECT * FROM county_bkp; didnt work because:

• It wouldnt let me insert in Primary Key column due toindentity_insert being off by default.
• My table had TimeStamp columns.

In that case:

• allow identity_insert in the OriginalTable
• insert query in which you mention all the columns of OriginalTable (Excluding TimeStamp Columns) and in Values select all columns from BackupTable (Excluding TimeStamp Columns)
• restrict identity_insert in the OriginalTable at the end.

EXAMPLE:

Set Identity_insert OriginalTable ON    insert into OriginalTable (a,b,c,d,e, ....) --[Exclude TimeStamp Columns here]    Select a,b,c,d,e, .... from BackupTable --[Exclude TimeStamp Columns here]    Set Identity_insert OriginalTable Off