How to restore one table from a backup file?? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to restore one table from a backup file??

Usual,when I need to restore one table from a backup file,I have to restore whole file into a new database,and then get the table from that new database.It really pained me! Any suggestion? Thanks!! Jelly.
Create the table on a different filegroup. Take the back up of individual file groups and when restoring, restore the filegroup which has the tables and apply the transaction log. This way u can restore just one table and don’t need tpo restore the entire database. hth. Gaurav
From SQL 7 restoring table from a backup has been disabled, you can follow as specified by Gaurav and also using DTS job to export/import rows for that table alone. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Satya, I am not aware if you can use DTS for reading data from a backup file. Is this true or I am missing something? Gaurav
Gaurav, thanks for point out. I mean using DTS with tables after you restore from that backup file, not with backup file. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

This is a workable plan for as long as there are few tables (which is not always the case) and most of the data modifications are unrelated (again not always the case). You sure can’t the compare the minor benefit of being able to restore a single table any time it is required to the hussle of managing, backing up, restoring numerous filegroups (and they can be numerous!). Think again. The light at the end of the tunnel could be a train heading your way. Nathan H.O.
Thanks all friends.
But I don’t think it’s a good idea what’s Gaurav said.
Because It’s impossible for me to create individual file group for every table,and usually before I need to restore the table ,I don’t know which table need to restore. Does any thirdparty tools to help ???
Let’s to continue discussing this subject … …
Jelly.
Why would you have the possibility of restoring any of ALL the tables in the database. I would consider some critical tables e.g. orders or transaction tables may be important. In case this high avaialbilty is required, having a good backup strategy will hwlp u out. Personally speaking, I do not approve the idea of restoring any of all the tables in the database. Gaurav
Thanks Gaurav and i agree with you. Usual I just need to restore one table,but i have to restore while backup file to a new database,and then export the table to the original database with DTS.
It’s a good idea to create the important tables on the different filegroups,then i just restore the one filegroup .but i still want to get a perfect method:just like oracle,we can export the tables which we want to restore from the backup file. Thanks Gaurav again. Jelly.
Other than DTS or table on individual filegroup, there is no way you can restore table from the database, its verified method. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Oh!That’s too bad!
but I am so glad to learnt a nice method of restore the table from the individual filegroup. Many thanks !!! Jelly.
If this is the case, then you can create DTS batch jobs that run at night and export the data from individual tables to text files / another database. This way you’ll always have the option of restoring just one table. This idea is not so good, but this is the only one that comes to my mind when it comes to SQL Server. Gaurav
]]>