Cannot reattach DB

Hi,
One of our DB's had to be detached yesterday and moved to
another drive while some work was done on the original
drive. It was then transfered back to its original drive
and then I tried to reattach it but I came up with the
following error msg:
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'G:\Data\2002Data_Data.MDF' is not a
valid database file header. The PageAudit property is
incorrect.
I need to mention that before detaching the DB it was
marked as 'Suspect' and would not open the error message
said something like NOLOCK / lack of space but we had to
carry on and detach the DB.
I have done some research on newsgroup and some suggest
creating a blank DB, stop SQL, rename the newly created DB
MDF file with the original (suspect) DB files. How do you
do this? I have tried:
ALTER DATABASE [02Data] --> dummy DB name
MODIFY FILE
(
NAME = '02Data_data', -->logical file name
filename = [2002Data_Data.MDF] --> new file name (the one
I'm trying to overwritte)
)
All this is happening on SQL2000 no SQL 7 involved.
Also you can't modify the filename from ETS manager.
Any ideas'
Many thanks> I have done some research on newsgroup and some suggest
> creating a blank DB, stop SQL, rename the newly created DB
> MDF file with the original (suspect) DB files. How do you
> do this?
Move away the "production" mdf file to somewhere else. Create the new database, and then you
*create* it, you specify the same name as the production mdf file name. Then delete the newly
created mdf file. And then copy the prod mdf file to the plae where it should be (where the newly
created one points).
And this applies for the ldf file as well. If you don't have an ldf file (lessens the chance to
suceed even more), then delete the newly created one to see if SQL Server will create one st
startup.
I don't give you much hope on this, so be prepared to restore from latest OK backup or open a case
with MS support (who *might* be able to help you).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Elisabeth Smith" <lise_bisou@.hotmail.com> wrote in message
news:0db401c36706$d2bf6850$a101280a@.phx.gbl...
> Hi,
> One of our DB's had to be detached yesterday and moved to
> another drive while some work was done on the original
> drive. It was then transfered back to its original drive
> and then I tried to reattach it but I came up with the
> following error msg:
> Server: Msg 5172, Level 16, State 15, Line 1
> The header for file 'G:\Data\2002Data_Data.MDF' is not a
> valid database file header. The PageAudit property is
> incorrect.
> I need to mention that before detaching the DB it was
> marked as 'Suspect' and would not open the error message
> said something like NOLOCK / lack of space but we had to
> carry on and detach the DB.
> I have done some research on newsgroup and some suggest
> creating a blank DB, stop SQL, rename the newly created DB
> MDF file with the original (suspect) DB files. How do you
> do this? I have tried:
> ALTER DATABASE [02Data] --> dummy DB name
> MODIFY FILE
> (
> NAME = '02Data_data', -->logical file name
> filename = [2002Data_Data.MDF] --> new file name (the one
> I'm trying to overwritte)
> )
>
> All this is happening on SQL2000 no SQL 7 involved.
> Also you can't modify the filename from ETS manager.
> Any ideas'
> Many thanks
>|||You CANNOT delete the newly created mdf file because of
sharing violation! if you detach the db beforehand and
delete the file and replace it with the bad file then
you're back to square one when trying to reattach!
>--Original Message--
>> I have done some research on newsgroup and some suggest
>> creating a blank DB, stop SQL, rename the newly created
DB
>> MDF file with the original (suspect) DB files. How do
you
>> do this?
>Move away the "production" mdf file to somewhere else.
Create the new database, and then you
>*create* it, you specify the same name as the production
mdf file name. Then delete the newly
>created mdf file. And then copy the prod mdf file to the
plae where it should be (where the newly
>created one points).
>And this applies for the ldf file as well. If you don't
have an ldf file (lessens the chance to
>suceed even more), then delete the newly created one to
see if SQL Server will create one st
>startup.
>I don't give you much hope on this, so be prepared to
restore from latest OK backup or open a case
>with MS support (who *might* be able to help you).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Elisabeth Smith" <lise_bisou@.hotmail.com> wrote in
message
>news:0db401c36706$d2bf6850$a101280a@.phx.gbl...
>> Hi,
>> One of our DB's had to be detached yesterday and moved
to
>> another drive while some work was done on the original
>> drive. It was then transfered back to its original drive
>> and then I tried to reattach it but I came up with the
>> following error msg:
>> Server: Msg 5172, Level 16, State 15, Line 1
>> The header for file 'G:\Data\2002Data_Data.MDF' is not a
>> valid database file header. The PageAudit property is
>> incorrect.
>> I need to mention that before detaching the DB it was
>> marked as 'Suspect' and would not open the error message
>> said something like NOLOCK / lack of space but we had to
>> carry on and detach the DB.
>> I have done some research on newsgroup and some suggest
>> creating a blank DB, stop SQL, rename the newly created
DB
>> MDF file with the original (suspect) DB files. How do
you
>> do this? I have tried:
>> ALTER DATABASE [02Data] --> dummy DB name
>> MODIFY FILE
>> (
>> NAME = '02Data_data', -->logical file name
>> filename = [2002Data_Data.MDF] --> new file name (the
one
>> I'm trying to overwritte)
>> )
>>
>> All this is happening on SQL2000 no SQL 7 involved.
>> Also you can't modify the filename from ETS manager.
>> Any ideas'
>> Many thanks
>
>.
>|||try stopping the sql service before you delete the original
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Elisabeth Smith" <lise_bisou@.hotmail.com> wrote in message
news:04b901c3670f$e6bc0d00$a001280a@.phx.gbl...
> You CANNOT delete the newly created mdf file because of
> sharing violation! if you detach the db beforehand and
> delete the file and replace it with the bad file then
> you're back to square one when trying to reattach!
>
> >--Original Message--
> >> I have done some research on newsgroup and some suggest
> >> creating a blank DB, stop SQL, rename the newly created
> DB
> >> MDF file with the original (suspect) DB files. How do
> you
> >> do this?
> >
> >Move away the "production" mdf file to somewhere else.
> Create the new database, and then you
> >*create* it, you specify the same name as the production
> mdf file name. Then delete the newly
> >created mdf file. And then copy the prod mdf file to the
> plae where it should be (where the newly
> >created one points).
> >And this applies for the ldf file as well. If you don't
> have an ldf file (lessens the chance to
> >suceed even more), then delete the newly created one to
> see if SQL Server will create one st
> >startup.
> >
> >I don't give you much hope on this, so be prepared to
> restore from latest OK backup or open a case
> >with MS support (who *might* be able to help you).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Elisabeth Smith" <lise_bisou@.hotmail.com> wrote in
> message
> >news:0db401c36706$d2bf6850$a101280a@.phx.gbl...
> >> Hi,
> >>
> >> One of our DB's had to be detached yesterday and moved
> to
> >> another drive while some work was done on the original
> >> drive. It was then transfered back to its original drive
> >> and then I tried to reattach it but I came up with the
> >> following error msg:
> >>
> >> Server: Msg 5172, Level 16, State 15, Line 1
> >> The header for file 'G:\Data\2002Data_Data.MDF' is not a
> >> valid database file header. The PageAudit property is
> >> incorrect.
> >>
> >> I need to mention that before detaching the DB it was
> >> marked as 'Suspect' and would not open the error message
> >> said something like NOLOCK / lack of space but we had to
> >> carry on and detach the DB.
> >>
> >> I have done some research on newsgroup and some suggest
> >> creating a blank DB, stop SQL, rename the newly created
> DB
> >> MDF file with the original (suspect) DB files. How do
> you
> >> do this? I have tried:
> >>
> >> ALTER DATABASE [02Data] --> dummy DB name
> >> MODIFY FILE
> >> (
> >> NAME = '02Data_data', -->logical file name
> >> filename = [2002Data_Data.MDF] --> new file name (the
> one
> >> I'm trying to overwritte)
> >> )
> >>
> >>
> >> All this is happening on SQL2000 no SQL 7 involved.
> >>
> >> Also you can't modify the filename from ETS manager.
> >>
> >> Any ideas'
> >>
> >> Many thanks
> >>
> >
> >
> >.
> >|||Yeah I clicked eventually... Cheers mate.
>--Original Message--
>try stopping the sql service before you delete the
original
>--
>BR,
>Mark Broadbent mcdba,mcse+i
>_________________________
>"Elisabeth Smith" <lise_bisou@.hotmail.com> wrote in
message
>news:04b901c3670f$e6bc0d00$a001280a@.phx.gbl...
>> You CANNOT delete the newly created mdf file because of
>> sharing violation! if you detach the db beforehand and
>> delete the file and replace it with the bad file then
>> you're back to square one when trying to reattach!
>>
>> >--Original Message--
>> >> I have done some research on newsgroup and some
suggest
>> >> creating a blank DB, stop SQL, rename the newly
created
>> DB
>> >> MDF file with the original (suspect) DB files. How do
>> you
>> >> do this?
>> >
>> >Move away the "production" mdf file to somewhere else.
>> Create the new database, and then you
>> >*create* it, you specify the same name as the
production
>> mdf file name. Then delete the newly
>> >created mdf file. And then copy the prod mdf file to
the
>> plae where it should be (where the newly
>> >created one points).
>> >And this applies for the ldf file as well. If you don't
>> have an ldf file (lessens the chance to
>> >suceed even more), then delete the newly created one to
>> see if SQL Server will create one st
>> >startup.
>> >
>> >I don't give you much hope on this, so be prepared to
>> restore from latest OK backup or open a case
>> >with MS support (who *might* be able to help you).
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?oi=djq&as
>> ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Elisabeth Smith" <lise_bisou@.hotmail.com> wrote in
>> message
>> >news:0db401c36706$d2bf6850$a101280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> One of our DB's had to be detached yesterday and
moved
>> to
>> >> another drive while some work was done on the
original
>> >> drive. It was then transfered back to its original
drive
>> >> and then I tried to reattach it but I came up with
the
>> >> following error msg:
>> >>
>> >> Server: Msg 5172, Level 16, State 15, Line 1
>> >> The header for file 'G:\Data\2002Data_Data.MDF' is
not a
>> >> valid database file header. The PageAudit property is
>> >> incorrect.
>> >>
>> >> I need to mention that before detaching the DB it was
>> >> marked as 'Suspect' and would not open the error
message
>> >> said something like NOLOCK / lack of space but we
had to
>> >> carry on and detach the DB.
>> >>
>> >> I have done some research on newsgroup and some
suggest
>> >> creating a blank DB, stop SQL, rename the newly
created
>> DB
>> >> MDF file with the original (suspect) DB files. How do
>> you
>> >> do this? I have tried:
>> >>
>> >> ALTER DATABASE [02Data] --> dummy DB name
>> >> MODIFY FILE
>> >> (
>> >> NAME = '02Data_data', -->logical file name
>> >> filename = [2002Data_Data.MDF] --> new file name
(the
>> one
>> >> I'm trying to overwritte)
>> >> )
>> >>
>> >>
>> >> All this is happening on SQL2000 no SQL 7 involved.
>> >>
>> >> Also you can't modify the filename from ETS manager.
>> >>
>> >> Any ideas'
>> >>
>> >> Many thanks
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
skrev i melding news:uI171jwZDHA.1644@.TK2MSFTNGP10.phx.gbl...
> > I have done some research on newsgroup and some suggest
> > creating a blank DB, stop SQL, rename the newly created DB
> > MDF file with the original (suspect) DB files. How do you
> > do this?
> Move away the "production" mdf file to somewhere else. Create the new
database, and then you
> *create* it, you specify the same name as the production mdf file name.
Then delete the newly
> created mdf file. And then copy the prod mdf file to the plae where it
should be (where the newly
> created one points).
> And this applies for the ldf file as well. If you don't have an ldf file
(lessens the chance to
> suceed even more), then delete the newly created one to see if SQL Server
will create one st
> startup.
> I don't give you much hope on this, so be prepared to restore from latest
OK backup or open a case
> with MS support (who *might* be able to help you).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Elisabeth Smith" <lise_bisou@.hotmail.com> wrote in message
> news:0db401c36706$d2bf6850$a101280a@.phx.gbl...
> > Hi,
> >
> > One of our DB's had to be detached yesterday and moved to
> > another drive while some work was done on the original
> > drive. It was then transfered back to its original drive
> > and then I tried to reattach it but I came up with the
> > following error msg:
> >
> > Server: Msg 5172, Level 16, State 15, Line 1
> > The header for file 'G:\Data\2002Data_Data.MDF' is not a
> > valid database file header. The PageAudit property is
> > incorrect.
> >
> > I need to mention that before detaching the DB it was
> > marked as 'Suspect' and would not open the error message
> > said something like NOLOCK / lack of space but we had to
> > carry on and detach the DB.
> >
> > I have done some research on newsgroup and some suggest
> > creating a blank DB, stop SQL, rename the newly created DB
> > MDF file with the original (suspect) DB files. How do you
> > do this? I have tried:
> >
> > ALTER DATABASE [02Data] --> dummy DB name
> > MODIFY FILE
> > (
> > NAME = '02Data_data', -->logical file name
> > filename = [2002Data_Data.MDF] --> new file name (the one
> > I'm trying to overwritte)
> > )
> >
> >
> > All this is happening on SQL2000 no SQL 7 involved.
> >
> > Also you can't modify the filename from ETS manager.
> >
> > Any ideas'
> >
> > Many thanks
> >
MSSQL2000
I had the same problem on some databases there was gonedown when there was
an error on a RAID controller and disks. There was NO WAY for me to reattach
or fix the mdf file so it will work 100%. I have tried everything thats
possible. The result was that i needed to use a 3d part utility called
MSSQLRecovery version 1.5 to extract the data and manualy check all the data
and then copied the data on to the SQL server and there was/is a hell of a
jobb. I also called MS, but they coudn't do anything with the file(s). So
good luck. SQL files sucks when there is something wrong with dem. So from
now i only trust the backup made by Veritas Backup Exec on Tape.
/Per W.