can i restore only a table from a backup file?

Hi,

i have a backup file , and i saw the wizard to restore the file in sql2005.

But i need to restore only a table.

Is there a wizard for that?

Hi, i did a transaction log backup of a database.

now i try to restore and i get this:

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Am i going in the right direction though?

|||

As of now i hd never seen a way to restore a table from a backup file. All you can do is create a new database and restore the backed up data file database into it and try to get a table required by you.

|||

This is a too easy way out,

but how come no such option exist?

If i have my db.bak,

why can i not be able to only backup one table from it? umm will it be becauseee. .. relationships, data changed since the backup , okay table a . pk a1, tb b pk,fk b1.

if i have to backup only table a1 , and from the backup time to now, table b contains related records it will no more find if i only backup a.

okay for now i still need to restore 1 stand alone table from a .bak file.

i am not able to do that too.. any guidance on that. straight forward using the wizard.

i have the db online. the .bak file. okay what do i do/ ? i am getting errors for now.

|||

In SQL, Single table restoration is possible, only when the table placed in a seperate filegroup and the filegroup backed up as well then you can restore the table alone wherever you like.

Otherwise you can not restore a table alone from a normal backup.

|||okay how do i do the above? can you please explain stepwise. thanks|||

You can also do a snapshot restore. If you have a snapshot backup, you can do an INSERT..SELECT statement and point the SELECT to your snapshot backup

|||

Refer to the SQL Books ONline in this case when it is explained with code examples.

hrubesh wrote:

okay how do i do the above? can you please explain stepwise. thanks

|||

Unless your database is laid out with the table in question in a separate filegroup before you find yourself needing to restore, you cannot use that approach. Also note that when restoring an individual filegroup, it MUST be brought up to the same point in time as the rest of the database in order to bring it online. So, if you're trying to recover from a mistake ("I didn't really mean to truncate that table!!") this won't help, as you'd need to roll the filegroup forward, including the mistake.

One way in which you can make filegroups work for you if you have enterprise or developer editions is this: You can restore the primary filegroup to a new database instance, and then restore only the filegroup containing the table you need to restore. At that point, the database is online, and you can SELECT out of it and into your production database.

At this point in time, SQL Server does not allow the restoration of a single table from a backup. Reasons include concerns of referential integrity as well as the logistics of accomplishing the task. It is high on the list of requests, and we are considering it for some future release (but not Katmai).