In our last couple of posts we focused our lenses on partial database restores.
Those posts are here:
- SQL Server – Database Restore – “Defunct State”
- SQL Server – Database Restore – Skipping File Groups
In this post we will look a bit more at the benefits and costs of restoring just a portion of database.
- Table Name :- dbo.ten95B
- File Group :- PRIMARY
- Text filegroup :- fgLOB
- Data Space
- 1370 MB
- or 1.4 GB
- Row Count :- 4800
Database – Files
Let us query the sys.database_files system table to get the name, type, state, and size of the individual files that are in our current database.
select tblSDF.[file_id] , tblSDF.[name] , tblSDF.[physical_name] , tblSDF.[type_desc] , tblSDF.[state_desc] , tblSDF.size -- , tblSDF.* from sys.database_files tblSDF
Database – Show File Stats
Let us issue “DBCC ShowFileStats” to gather information about our database files.
Let us try to read data from table
Query Table for Data – All Columns
select top (1000) * from [dbo].[ten95C]
Large object (LOB) data for table “dbo.ten95C” resides on an offline filegroup (“fgLOB”) that cannot be accessed
Query Table for Data – Specific Columns
On the other hand, we are still able to query for specific columns.
When we restrict our column set to those filegroups that were included in our restore, things are good.
select top (10) tblT.[ten95C_id] , tblT.[ten95C_view_cnt] , tblT.[ten95C_view_last] from [dbo].[ten95C] tblT
From a cursory look, Microsoft’s design and implementation of Partial database restore is well thought out and solid.
Its preserves all the niceties of having access to the original database design ( database groups and files), metadata for all concerns.
And, rightfully sacrifices storage of the actual data and thus we gain the benefits of not having to provide and maintain storage for uneeded data.