Retrieve data from First 25 and last 25 data pages from the Database

Question

Retrieve data from First 25 and last 25 data pages from the Database

MillionQueries

Old Hand

Points: 369
More actions
February 11, 2021 at 2:38 pm

#3843834

I have received a very strange and unusual request from one of our departments to provide them the data for first 25 and last 25 pages of the database since they would like to copyright that data.
My argument is that database is made up of multiple objects like tables, stored procedures, functions etc which is used to retrieve information. and those can be stored anywhere on the hard disk.....and I run the index reorg and rebuild very frequently so the data pages would keep on moving in the hard disk so data in first 25 and last 25 data pages will never be consistent.
Is is even possible to retrieve this kind of information at the data page level.
When i did my research, i found that there is a command like DBCC Page and DBCC IND which can provide some insight but if the request is to retrieve data from first 25 and last 25 data pages from the entire database then would that be possible?
Thank you in advance

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

I'm curious how they are planning to "copyright" it ... and what the front-and-back portions actually achieves in practice.

How about: take a backup, post it to yourself, make sure the postmark is clearly legible, do NOT open the envelop ... until you get to court 🙂

Hope that devices still exist at that mythical future date to read the ... floppy disk?!

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

>they would like to copyright that data

This sounds pointless.

If you are selling an application to customers and consider some of the data as sensitive you should try:

hosting the sensitive data yourself and getting your application to do RPCs over the internet to obtain it.
putting the sensitive data in a separate highly encrypted datastore, not SQL Server, and getting your application to read from that.
hosting the complete application yourself.

This reply was modified 3 years, 9 months ago by Ken McKelvey.

MillionQueries Old Hand Points: 369 More actions · Answer 3

I agree @ken and @kristen

But is it even possible to get the first 25 and last 25 pages of data from the data pages since Data pages constantly keeps moving during Index Reorg and Rebuild...isnt it?

This reply was modified 3 years, 9 months ago by MillionQueries.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

MillionQueries wrote:

But is it even possible to get the first 25 and last 25 pages of data from the data pages since Data pages constantly keeps moving during Index Reorg and Rebuild...isnt it?

Do they actually mean that? or are they meaning "First 25 pages starting at A" and "Last 25 pages ending at Z"?

Maybe this is a bit like circulating versions of a document with subtle spelling / grammatic / vocabulary changes on each so that when someone leaks it to the press you know who did it. Similar with fictitious / error names on maps to catch copycats and so on ... if that's the case then maybe any random data sample (which could then be found in a rip-off copy) would be sufficient?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

you can - but it would be completely useless for any intents and purposes. small c# program open file binary, read first 25 pages (25*8k bytes) and do same for last 25 pages.

now... either of these could be completely empty at the point in time you do it.

MillionQueries Old Hand Points: 369 More actions · Answer 6

Thanks @frederico, but wouldn't the data pages keep on changing every time we run index reorg or index rebuild?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

MillionQueries wrote:

I agree @ken and @kristen
But is it even possible to get the first 25 and last 25 pages of data from the data pages since Data pages constantly keeps moving during Index Reorg and Rebuild...isnt it?

No. Unless the table is 100% static (and it's not or you wouldn't need to do index maintenance) and it's the only table in the database and it only has a clustered index, the index maintenance could move them every time. Data updates, especially if they're "ExpAnsive" updates, can easily cause page splits and it only takes one to screw things up for what they're ask.

Yes, we can get the first 25 and last 25 pages but the things I mention above could make any of those different at the drop of a hat... any hat... the hat doesn't even need to hit the floor.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 8

MillionQueries wrote:

Thanks @frederico, but wouldn't the data pages keep on changing every time we run index reorg or index rebuild?

yes - but by giving them those pages on a file as per requirement they may then see the stupidity of their request and get back to real world and agree with you on a valid set of requirements.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Heh... might as well show that you're flexible, as well. Ask them if they want that in logical page order or physical page order. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

When they say "pages" are they actually thinking about something like a Yabingle search result? Get the first 25 "pages" could mean the first set of results and the last set of results.

Sorry, trying to speculate on how this might not be a completely stupid request.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MillionQueries Old Hand Points: 369 More actions · Answer 11

Hi @frederico, Thank you, so to fulfill that request to get the data from data pages, can you please provide me some insight and/or documentation to how I can actually perform that query?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 12

the following code will work - .mdf file CAN NOT be opened by SQL Server when it is done as SQL will exclusively lock the file.

as everyone said this is a dumb requirement and data contents will change constantly, and depending on how database was setup last 25 pages may be completely garbish, all zeros or even contain data unrelated to this db (if IFI is enabled on the instance and the file was created over other "deleted" data

using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        /*
         
            call passing requried parameters source file, file for first 25 pages, file for last 25 pages
            C:\sql_server\MSSQL13.SQL2016\MSSQL\DATA\test.mdf c:\sql_server\file1.bin c:\sql_server\file2.bin

         */
        static void Main(string[] args) // static with a void (or int) return type
        {
            var sourcename = args[0];
            var outfilefirst25pages = args[1];
            var outfilelast25pages = args[2];

            // 25 * 8Kb Pages
            int desiredsize = 25 * 8 * 1024;

            // write first 25 pages
            using (BinaryReader binReader = new BinaryReader(File.Open(sourcename, FileMode.Open, FileAccess.Read)))
            {
                long length = (int)binReader.BaseStream.Length;

                // if file size is less than length of file adjust the size
                desiredsize = desiredsize > length ? (int)length : desiredsize;
                binReader.BaseStream.Seek(0, SeekOrigin.Begin);
                using (BinaryWriter outputFile = new BinaryWriter(File.Open(outfilefirst25pages, FileMode.Create)))
                {
                    outputFile.Write(binReader.ReadBytes(desiredsize));
                }
            }

            // write last 25 pages
            using (BinaryReader binReader = new BinaryReader(File.Open(sourcename, FileMode.Open, FileAccess.Read)))
            {
                long length = (int)binReader.BaseStream.Length;

                // if file size is less than length of file adjust the size
                desiredsize = desiredsize > length ? (int)length : desiredsize;

                binReader.BaseStream.Seek(-desiredsize, SeekOrigin.End);

                using (BinaryWriter outputFile = new BinaryWriter(File.Open(outfilelast25pages, FileMode.Create)))
                {
                    outputFile.Write(binReader.ReadBytes(desiredsize));
                }
            }
        }
    }
}

This reply was modified 3 years, 9 months ago by frederico_fonseca.