locked
page structure for a data page RRS feed

  • Question

  • Please help to provide a page structure for data page, I want to got table list , table structure and table data from a failed mdf file, thank you
    Wednesday, September 5, 2018 7:40 AM

Answers

  • <<failed mdf file,?

    Have you ran DBCC CHECKDB? What is the output?

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server?view=sql-server-2017

    select * from msdb..suspect_pages


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 5, 2018 9:54 AM
  • You can find details of some page structures from Paul Randal's blog series (https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/) and from Kalen Delaney's Inside SQL Server 2012 Internals book (https://www.amazon.com/Microsoft-Server-Internals-Developer-Reference/dp/0735658560).

    Salvaging data from a damaged file may be exceedingly difficult and time consuming. There are third party tools that proport being able to do this, though. You might try a internet search. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, September 6, 2018 2:06 AM
    Answerer
  • Take a look at DBCC PAGE command 

    http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx

    use master
    go

    CREATE procedure sp_object_from_page 
    (
       @page int,      -- database page number
       @file int = 1   -- if not the default, specify filenumber
    )
    as
    /*
       Used to get an object name from a page number (some
       deadlock traces tend to report just a page number)
       Run the procedure in the database the object resides
       Use at own risk !!
    */
    set nocount on

    declare @cmd nvarchar(300)
    declare @id varchar(50)
    create table #page(ParentObject sysname,
                       Object       sysname,
                       Field        sysname,
                       Value        sysname)

    set @cmd = N'dbcc page(''' + DB_NAME() + N''',' +
                 cast(@file as nvarchar(5)) + N',' +
                 cast(@page as nvarchar(10)) + N',0) with tableresults,no_infomsgs'

    insert #page
    exec(@cmd)

    select @id = Value from #page
    where ParentObject = 'PAGE HEADER:'
    and Field = 'm_objId'

    select ISNULL(object_name(@id),'Not Found') as 'Object Name'

    drop table #page
    return
    go


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, September 6, 2018 6:43 AM
  • I get a badly damaged database file and I want to read as much data as possible from the inside, so I can only start with the file and not from DBCC

    I would also suggest some third part tool. You may see below link

    https://social.technet.microsoft.com/wiki/contents/articles/51727.sql-server-how-to-recover-from-corrupt-sql-server-database-when-recovery-via-tsql-might-not-be-possible.aspx

    Please note I did some test using TP tool in above link and I was able to get the data. If you feel you do not want to go that way feel free not to use the tool.  If you feel you can get most of the data by hack attaching mdf file some way please try that method, but with prior experience the chances are very rare.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, September 6, 2018 6:50 AM
    Answerer

All replies

  • Please help to provide a page structure for data page, I want to got table list , table structure and table data from a failed mdf file, thank you

    Are you asking about SQL Server data page. See below

    Anatomy of Page


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 5, 2018 7:56 AM
    Answerer
  • <<failed mdf file,?

    Have you ran DBCC CHECKDB? What is the output?

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server?view=sql-server-2017

    select * from msdb..suspect_pages


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 5, 2018 9:54 AM
  • I get a badly damaged database file and I want to read as much data as possible from the inside, so I can only start with the file and not from DBCC
    Thursday, September 6, 2018 1:19 AM
  • You can find details of some page structures from Paul Randal's blog series (https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/) and from Kalen Delaney's Inside SQL Server 2012 Internals book (https://www.amazon.com/Microsoft-Server-Internals-Developer-Reference/dp/0735658560).

    Salvaging data from a damaged file may be exceedingly difficult and time consuming. There are third party tools that proport being able to do this, though. You might try a internet search. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, September 6, 2018 2:06 AM
    Answerer
  • Take a look at DBCC PAGE command 

    http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx

    use master
    go

    CREATE procedure sp_object_from_page 
    (
       @page int,      -- database page number
       @file int = 1   -- if not the default, specify filenumber
    )
    as
    /*
       Used to get an object name from a page number (some
       deadlock traces tend to report just a page number)
       Run the procedure in the database the object resides
       Use at own risk !!
    */
    set nocount on

    declare @cmd nvarchar(300)
    declare @id varchar(50)
    create table #page(ParentObject sysname,
                       Object       sysname,
                       Field        sysname,
                       Value        sysname)

    set @cmd = N'dbcc page(''' + DB_NAME() + N''',' +
                 cast(@file as nvarchar(5)) + N',' +
                 cast(@page as nvarchar(10)) + N',0) with tableresults,no_infomsgs'

    insert #page
    exec(@cmd)

    select @id = Value from #page
    where ParentObject = 'PAGE HEADER:'
    and Field = 'm_objId'

    select ISNULL(object_name(@id),'Not Found') as 'Object Name'

    drop table #page
    return
    go


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, September 6, 2018 6:43 AM
  • I get a badly damaged database file and I want to read as much data as possible from the inside, so I can only start with the file and not from DBCC

    I would also suggest some third part tool. You may see below link

    https://social.technet.microsoft.com/wiki/contents/articles/51727.sql-server-how-to-recover-from-corrupt-sql-server-database-when-recovery-via-tsql-might-not-be-possible.aspx

    Please note I did some test using TP tool in above link and I was able to get the data. If you feel you do not want to go that way feel free not to use the tool.  If you feel you can get most of the data by hack attaching mdf file some way please try that method, but with prior experience the chances are very rare.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, September 6, 2018 6:50 AM
    Answerer