Suppose there is a huge database and backup file to it.

Something went wrong and some of the tables were corrupted.

Is it possible not to shoot from a cannon on sparrows and from a * .bak file to pick up certain tables without restoring the entire database, which can be very time consuming?

If not, for what reason is this useful feature missing?

I know about transaction logs, but in this question I consider taking a certain piece from backup.

  • Tables can not be restored. But you can restore the page if you can understand what you need. Once we went this way, but the work was not done by me, for this process I understood poorly. Perhaps this article on Habré can help you habrahabr.ru/post/137301 - Viktorov

2 answers 2

Not. there is no possibility. Backup from SQL is not just a set of structures and data, it is actually a dump of the entire database structure with indices and other things, while preserving the actual location of the data. Those. physically, the data in the table can be spread throughout the backup. Besides, how do you imagine the recovery of a single table, what structure should it go into? If you try to restore in the current database, you get integrity problems (keys, indexes), if you create a separate one and create only this table in it, this is a very specific task.

For your case, I can offer the following options: look at the programs that are trying to recover data from corrupted backups (which independently analyze the backup structure), or restore the backup to a neighboring database (can be on another server), and retrieve the data you need.

    There is no such possibility in SQL Server.

    For whatever reason - the question is not to the community, but to Microsoft. I can only make the assumption that the reason is that the backup is done not routinely, but page by page, i.e. There are no easily available metadata in the backup with information about exactly which pages your table is stored on and which pages need to be restored. In addition, if it suddenly turns out that in your database the page has changed the owner and it is no longer a table, but another object, then you need to work out additional recovery logic - when the page is restored not to its own place, but to a new one, change the data structure (you need to change all links to this page).

    Apparently, Microsoft considers such work inexpedient, so the only option for partial recovery is to restore files, file groups, and individual pages.

    • I corrected myself - you can still restore individual pages. - minamoto