Restoring Single table frombackup

Question

Restoring Single table frombackup

JakDBA

SSCommitted

Points: 1610
More actions
September 15, 2010 at 4:05 pm

#224070

Please advice its very urgent
How to restore Single table from fullDBbackup?
Working on SQL serer 2008r2
@JayMunnangi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16235 More actions · Answer 1

You cannot restore a single table from a native SQL 2008 R2 backup. However, you can restore the entire database with a different name and then export the table and data to the original database.

JakDBA SSCommitted Points: 1610 More actions · Answer 2

Could you please explain me the procedure to do so,

You mean diffrent system or diffrent DB?

Please exlplain me the procedure

@JayMunnangi

JeremyE SSCoach Points: 16235 More actions · Answer 3

You could restore the backup to the same system with a different name or to a different system. Either will work. Then use the Import and Export wizard to copy the table back into the original database.

For example to restore database DB1 to the same system as a new name DB2:

RESTORE DATABASE DB2

FROM DISK = 'C:\DB1.bak'

WITH

MOVE 'DB1_data' TO 'C:\DB2_data.mdf',

MOVE 'DB1_log' TO 'C:\DB2_log.ldf'

GO

Then right click DB1, tasks, import data. Select DB2 as the source and select the table you want to import.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 4

There are 3rd party tools that can recover individual objects from a backup file, like Red Gate's SQL Object Level Recovery Native, if restoring the entire database is not an option. However, you might first want to check if the recovered data is transactionally consistent, as there might be issues as described here.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

mjkreddy1927 (9/15/2010)
Please advice its very urgent
How to restore Single table from fullDBbackup?
Working on SQL serer 2008r2

did you dropped the table? If yes, the If you have full backup then take the tail tlog backup and then do a point in time recovery.

"More Green More Oxygen !! Plant a tree today"

Imran Ashraf-452633 SSCommitted Points: 1969 More actions · Answer 6

Just be careful wih the point in time recovery the fact that you dropped the table may not have stopped people entering data into other tables in the DB so if you recover to a point before the table was dropped you would need to ensure that you got the data re-entered. Best idea is to restore DB to diff DB as first responder suggested and BCP the data out and into the table that was removed.