0

I have a 2.5TB bak file which is compressed and DB is 9TB

The tables have a couple of MDF files and then partitions for months of time series logging data

I want to read a certain table in this backup.

Is there a way to read the bak file (certain table) or restore only the table/primary file group and ignore the partitions

the current restore process is going to take a number of days and i need to check the backup for a bunch of rows that were deleted

I need a fast way to check the data in one table without waiting for the huge restore

sql server 2017 enterprise

Improve this question
1
  • 2
    "The tables have a couple of MDF files" - regardless of the file extension, a database can have only one primary database file (MDF extension by convention) plus zero or more secondary files (NDF extension by convention). Per the answer by @J.D., piecemeal restore requires restoring all files in the primary file group at a minimum. You don't need to bother restoring other filegroups if your table is on the primary filegroup. If the table is on a user-defined filegroup, you'll need to restore all files in that filegroup too.
    – Dan Guzman
    Commented Jun 23, 2023 at 13:42

1 Answer 1

Reset to default
1

I want to read a certain table in this backup.

Unfortunately, this is not possible natively in SQL Server. It's one of the top feature requests though, so feel free to vote on it.

You'll either need to restore the entire backup or start utilizing a 3rd party tool that does offer such a feature. Unfortunately, even most of the recommended 3rd party tools have been retired too.

Alternatively, there's a feature called a Piecemeal Restore in SQL Server. This may allow you to restore only the primary filegroup (since you mentioned you have multiple filegroups):

Piecemeal restore allows databases that contain multiple filegroups to be restored and recovered in stages. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. Piecemeal restore maintains checks to ensure that the database will be consistent in the end. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

And

Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups. During the piecemeal-restore sequence, the whole database must go offline. Thereafter, the database is online and restored filegroups are available. However, any unrestored filegroups remain offline and are not accessible. Any offline filegroups, however, can be restored and brought online later by a file restore.

Improve this answer
2
  • thanks, can you mention any of the 3rd party tools I can try and find to test?
    – Quade
    Commented Jun 23, 2023 at 13:41
  • @Quade Sorry, I don't think they're available anymore. But companies like RedGate, Apex, and Quest used to offer them.
    – J.D.
    Commented Jun 30, 2023 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.