April 10, 2013 at 2:05 pm
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!
April 10, 2013 at 2:57 pm
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
April 10, 2013 at 3:41 pm
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.
April 10, 2013 at 3:45 pm
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!
April 10, 2013 at 3:48 pm
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
April 11, 2013 at 5:07 am
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?
April 11, 2013 at 12:59 pm
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
April 11, 2013 at 1:28 pm
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?
April 11, 2013 at 2:07 pm
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
April 11, 2013 at 2:38 pm
Hmm, does that work with sp_msforeachdb? It's not liking what I'm putting in... Thank you for all the help!!!
April 11, 2013 at 2:42 pm
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
April 17, 2013 at 3:50 pm
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!!!
April 17, 2013 at 4:02 pm
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)' + ')').
April 17, 2013 at 4:21 pm
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!!
April 17, 2013 at 4:38 pm
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 22 total)
You must be logged in to reply to this topic. Login to reply