How to Recover ROW and PAGE Compressed Data in SQL Server?

The page in SQL Server is the fundamental unit to store data. It stores data and indexes. The rows are stored on pages of 8 KB. The pages are numbered and have an ID. The following query shows the page_id of the column, where the CustomerID is equal to 2 in the Sales.Customer table.

Query :
SELECT TOP 100 plc.*, [CustomerID]
FROM [Sales].[Customer] as c (nolock)
CROSS APPLY sys.fn_physLocCracker (%%physloc%%) As plc
WHERE c.CustomerID=2
GO

The pages can get corrupted due to various reasons, like virus attacks, malicious software, or hardware problems.

Row and Page Compression

In SQL Server, it is possible to compress the data at the row or page level. Data compression improves the I/O operation performance but increases the CPU usage. To enable data compression at the page level, the following T-SQL command can be used.

ALTER TABLE sales.customer REBUILD WITH (DATA_COMPRESSION = PAGE);

The above statement will modify the table – sale.customer and rebuild the table to use compression
at the page level. The below example shows how to compress data at the row level:

ALTER TABLE Sales.Customer
REBUILD WITH (DATA_COMPRESSION = ROW);

How to Recover Row and Page Compressed Data using SQL Server Tools?

Using the SQL Server Management Studio (SSMS), you can recover specific pages from a backup. For this, you will require a backup of the SQL database. The following command provides a backup of the database:

BACKUP DATABASE stellar
TO DISK = ‘C:\backup\stellar.bak’;

This command will create a backup of the database, named stellar, in the c:\ drive in the backup
folder and the stellar.bak file.
To restore a page, open Object Explorer, right-click on the database, and select Tasks > Restore >
Page.

You need to enter two things:

1. The file ID that contains a numeric identifier of the data file.
2. The page ID. You can get the page ID from the error message when you run the DBCC
   CHECKDB command:
   Msg 8909, Level 16, State 1, Line 1
   Table error: Object ID 13456, index ID 1, partition ID 4567890123, alloc unit
   ID 78901234 (type DATA), page ID 98765432 contains an incorrect page ID in its
   page header. The PageId in the page header = 543210.

To get the file ID, use this query:

SELECT name as FileName, file_id AS FileID
FROM sys.database_files;

You will see the file ID in the file_id column.
Now, press the add button, enter the File ID and the Page ID, and press OK.

After that, you will be able to recover the pages with errors.
Note: SQL Server allows to recover specific compressed pages, but not rows.

How to Recover Row and Page Compressed Data using a Third-Party Tool?

To recover compressed pages or rows, you can use a SQL Recovery tool, such as Stellar Repair for MS SQL. This software can repair the entire database, thus fixing the pages with errors. Once you download the software, you need to find your data file. The data file contains all the data and database pages. To find the data file, press the Find button :

Once you find the database file, take it offline and then make a copy.

Select the copy of the data file and press the Repair button to repair your database.

When the database is repaired, click the Save icon. You can save the repaired data in a new database
or in an existing database.

You can also save the data in other file formats, like Excel, CSV, and HTML. The software will export
the data from the repaired data file to the format of your preference.

Conclusion

In this article, we have discussed what the SQL Server pages are and how to compress data at row and page levels. We also mentioned the process of recovering specific SQL Server pages using tools in SQL Server. As there is no option in SQL Server to recover rows, you can use Stellar Repair for MS SQL to repair the database and recover the row and page compressed data.