DBCC on a detached MDF file

  • mike.hamilton721

    Old Hand

    Points: 360

    Hello,

    I am by no means above a SQL novice, but I am trying to figure out if there is a way to run DBCC commands (DBCC fileheader in particular) on an offline/detached MDF? Is this possible? If so, any help pointing me in the right direction? Thank you!

  • Paul Randal

    One Orange Chip

    Points: 29438

    Nope - not possible.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    Is there some reason you cannot attach this to another instance? If it's because of possible corruption, please make sure you copy the MDF before attempting.

    If you have potential data loss issues, please consider calling Microsoft CSS Support.

  • mike.hamilton721

    Old Hand

    Points: 360

    Thanks! Well I'm trying to combine all of my results from a exec sp_msforeachdb 'dbcc fileheader(?)' into 1 table so I can just select 2 of the columns instead of all. If they have to be online, that is fine also. The end result, is to print 2 of these columns into a document of some sort... Any ideas from any of you? Thanks for the replies!

  • Paul Randal

    One Orange Chip

    Points: 29438

    What data are you trying to gather from that? There may be an easier way to do it.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • mike.hamilton721

    Old Hand

    Points: 360

    I need to pull the logicalname and bindingid fields from fieldheader on multiple DBs, and place them in a text file. It looks like Windows Powershell might be able to do that too... What was your idea?

  • Paul Randal

    One Orange Chip

    Points: 29438

    Hmm - no other way to get those AFAIK. I was hoping you were after some information that was stored in master for each database.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • mike.hamilton721

    Old Hand

    Points: 360

    Thanks! Is there a way to compile all of the msforeachdb dbCC fileheader results into 1 page with just those 2 columns (I can't figure out how to do a select from dbcc fileheader), then write to the tempdb, then export that to a CSV?

  • Paul Randal

    One Orange Chip

    Points: 29438

    Create a temp table and then INSERT into the table EXEC (string variable with the DBCC fileheader command in).

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • mike.hamilton721

    Old Hand

    Points: 360

    Hmm, does that work with sp_msforeachdb? It's not liking what I'm putting in... Thank you for all the help!!!

  • Paul Randal

    One Orange Chip

    Points: 29438

    I haven't tried it, but it should do. My guess is the number of ' are tripping you up.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • mike.hamilton721

    Old Hand

    Points: 360

    Alright. Played around (and Googled), and came up with this:

    DBCC traceon (3604);

    drop table #DBID

    Create table #DBID (

    Name nvarCHAR(256)NULL,

    LogicalName nvarCHAR(256)NULL,

    BindingID nvarCHAR(256)NULL)

    declare @db varchar(256),

    @dbid int,

    @hidb int

    select @hidb = MAX(dbid),

    @dbid = 5

    from sysdatabases

    while @dbid <= @hidb

    begin

    set @db = null

    select @db = name

    from sysdatabases where dbid = @dbid

    if @db is not null

    Declare @Cmd varchar(8000)

    insert into #DBID (LogicalName,BindingID)

    EXEC ('DBCC fileheader(@dbid)')

    insert into #DBID (Name) Values (@db)

    set @dbid = @dbid + 1

    end

    dbcc traceoff(3604)

    select Name,LogicalName,BindingID from #DBID

    -----------------------------------

    Now the problem is that it doesn't like the @dbid in the EXEC command. So it pulls all the names as it scrolls through the DB's, but I get the "Must declare the scalar variable @dbid" message. Any ideas on how to work around that? Thank you for the help!!!

  • Lynn Pettis

    SSC Guru

    Points: 442091

    mike.hamilton721 (4/17/2013)


    Alright. Played around (and Googled), and came up with this:

    DBCC traceon (3604);

    drop table #DBID

    Create table #DBID (

    Name nvarCHAR(256)NULL,

    LogicalName nvarCHAR(256)NULL,

    BindingID nvarCHAR(256)NULL)

    declare @db varchar(256),

    @dbid int,

    @hidb int

    select @hidb = MAX(dbid),

    @dbid = 5

    from sysdatabases

    while @dbid <= @hidb

    begin

    set @db = null

    select @db = name

    from sysdatabases where dbid = @dbid

    if @db is not null

    Declare @Cmd varchar(8000)

    insert into #DBID (LogicalName,BindingID)

    EXEC ('DBCC fileheader(@dbid)')

    insert into #DBID (Name) Values (@db)

    set @dbid = @dbid + 1

    end

    dbcc traceoff(3604)

    select Name,LogicalName,BindingID from #DBID

    -----------------------------------

    Now the problem is that it doesn't like the @dbid in the EXEC command. So it pulls all the names as it scrolls through the DB's, but I get the "Must declare the scalar variable @dbid" message. Any ideas on how to work around that? Thank you for the help!!!

    Two problems I see. First, you declare @dbid in the outer script but never populate it. Second, if it was populated, your not adding it to you dynamic sql correctly.

    This, EXEC ('DBCC fileheader(@dbid)'), is where you are getting you error since @dbid is not declared in the context of the dynamic sql. You probably want this: EXEC ('DBCC fileheader(' + cast(@dbid as varchar)' + ')').

  • mike.hamilton721

    Old Hand

    Points: 360

    Thank you Lynn. I tried that new EXEC you listed (missing a ' ?), and I thought I added it in the right spot. But I'm not sure what you mean about populating? I did

    @dbid = 5

    above the BEGIN string. Is that not how I should do this?

    The exec line now looks as follows:

    EXEC ('DBCC fileheader(''+cast(@dbid as varchar)'+')')

    This is over my head to be honest 🙂 Thank you for the helP!!

  • Lynn Pettis

    SSC Guru

    Points: 442091

    mike.hamilton721 (4/17/2013)


    Thank you Lynn. I tried that new EXEC you listed (missing a ' ?), and I thought I added it in the right spot. But I'm not sure what you mean about populating? I did

    @dbid = 5

    above the BEGIN string. Is that not how I should do this?

    The exec line now looks as follows:

    EXEC ('DBCC fileheader(''+cast(@dbid as varchar)'+')')

    This is over my head to be honest 🙂 Thank you for the helP!!

    Sorry, missed it in all the unformatted code.

Viewing 15 posts - 1 through 15 (of 23 total)

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