Disk Crash Recover - Won't Recover my SQL Database

My client had a hard disk crash, was running SQL 7.0. The disk engineers were
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LDF
transaction log is missing, it is refusing to do so. We were using a "simple"
log model, so I'm not sure if the transaction log even had anything to useful.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,IL
Have you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL
|||Yes I have tried sp_attach_single_file, but it still keeps on looking for the
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:

> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>
|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>
|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>

Disk Crash Recover - Won't Recover my SQL Database

My client had a hard disk crash, was running SQL 7.0. The disk engineers wer
e
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LD
F
transaction log is missing, it is refusing to do so. We were using a "simple
"
log model, so I'm not sure if the transaction log even had anything to usefu
l.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,ILHave you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL|||Yes I have tried sp_attach_single_file, but it still keeps on looking for th
e
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:

> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for
the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
>|||> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
Whether or not a log file is needed when you attach (or similar) a database
depends on whether there
is recovery work to do in the database. Every time a database starts, it wil
l see whether it has to
perform recovery work. Recovery work include REDO and UNDO of log records. T
his it has to do because
transactions might have been in flight when the database was shut down. If S
QL Server determines
that there is recovery work to be done, it *need* the log file.
SQL Server will not allow you to use an inconsistent database (as in the cas
e of missing log file
and it need to do recovery work).
I.e., you never know whether SQL Server can create the log file - so you sho
uld *never* rely on
this. (Paul - I welcome elaborations and/or corrections to this statement...
:-). )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Celal" <Celal@.discussions.microsoft.com> wrote in message
news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...[vbcol=seagreen]
> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
> "Rosman Computing" wrote:
>|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>|||Ahh, I tend to forget about the detach/offline cases. I guess my sceptism ab
out these things isn't
as much technical or mistrust, but more from these newsgroups. All the posts
where SQL Server cannot
create the log file. "I *did* detach the database first". Perhaps the simple
truth is:
a) The poster (not in this thread, I should add), claims that detach was per
formed even though it
wasn't performed. ...In some vain hope that claiming that fact would somehow
change things.
b) I'm polluted by posts where log files cannot be created, and I just don't
keep track of which
cases detach (or offline) actually happened.
I wish there could be some type of "FK"/link in NTFS so SQL Server could enf
orce that we cannot
delete log files unless it was shutdown cleanly (even when SQL Server is sto
pped). Also, there would
be nice if we could investigate this bit(?) in the mdf file header(?).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is
> running) then the log file won't be needed as there's nothing to recover.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
>|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>|||Hehe, I knew you were to say something like that...

> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
If we know what bit(?) it is, we could check it with a hex editor. Or, even
produce some tiny
utility that reads the beginning of the file, see what value the bit has and
present it. Heck, that
small utility could even be produced by MS ;-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eIKzFlvHHHA.3540@.TK2MSFTNGP02.phx.gbl...
> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
>

disk crash and dwh recover

hi everybody.
A customer of mine has had a disk "crash" few days ago.
I wrote for him a dwh application and
of course the backup was not working...
now some "hardware guys" said that it is possible to recover most of
the files.
what I need to restore would be:
1- the sql server db
2- the sql server dts and jobs
3- the analysis service cube and dimensions
to do so is it enough for me to have:
1- the .mdf and .ldf files
2- the master.mdl /.ldf file
3- the .odb file and .dim* and .role .map ... files
right?
is it enough to recover everything?
if it is so, what would be the right processes to recover everything?
any help greatly appreciated!No RAID and no backups? So this data warehouse is really just a bunch of
data dumped on someone's PC with a few reports tacked onto it? ;-)
Assuming the hardware gurus can actually recover the .mdb (data) files
intact, they can be reattached once the SQL Server box is re-installed. The
.ldf files are the transaction logs, and in a bulk loaded data warehouse,
restoring these may not be important.
http://msdn2.microsoft.com/en-us/library/ms190794.aspx
http://support.microsoft.com/defaul...kb;en-us;224071
What will be a pain is restoring or re-building the master database and how
this affects logins/users for the user database(s). However, unlike
operational databases, most data warehousing databases only have a limited
number of users or profiles (ex: Admin and Reporting), so perhaps this won't
be too much of a deal.
Rebuilding the master Database:
http://msdn.microsoft.com/library/d... />
t_0cvm.asp
User logons and permissions on a database may be incorrect after the
database is restored:
http://support.microsoft.com/defaul...kb;en-us;168001
As for restoring the Analysis Services cubes, I assume you at least have a
backup of the development system which can be restored to production. OLAP
data is typically just derived and aggregated, so once AS is re-installed
and the cube files restored, just re-process and they will be re-loaded from
the source data warehouse tables.
Microsoft SQL Server 2000 Analysis Services Operations Guide:
http://www.microsoft.com/technet/pr...n/anservog.mspx
"ilkaos" <c.caoduro@.gmail.com> wrote in message
news:1138637846.479954.273090@.f14g2000cwb.googlegroups.com...
> hi everybody.
> A customer of mine has had a disk "crash" few days ago.
> I wrote for him a dwh application and
> of course the backup was not working...
> now some "hardware guys" said that it is possible to recover most of
> the files.
> what I need to restore would be:
> 1- the sql server db
> 2- the sql server dts and jobs
> 3- the analysis service cube and dimensions
> to do so is it enough for me to have:
> 1- the .mdf and .ldf files
> 2- the master.mdl /.ldf file
> 3- the .odb file and .dim* and .role .map ... files
> right?
> is it enough to recover everything?
> if it is so, what would be the right processes to recover everything?
> any help greatly appreciated!
>|||
> No RAID and no backups? So this data warehouse is really just a bunch of
> data dumped on someone's PC with a few reports tacked onto it? ;-)
>
kind of...it was my first attempt with analysis service and it did a
"little but good" job..

> Assuming the hardware gurus can actually recover the .mdb (data) files
> intact, they can be reattached once the SQL Server box is re-installed.
The
> .ldf files are the transaction logs, and in a bulk loaded data warehouse,
> restoring these may not be important.
> http://msdn2.microsoft.com/en-us/library/ms190794.aspx
> http://support.microsoft.com/defaul...kb;en-us;224071
I thought so

> What will be a pain is restoring or re-building the master database and
how
> this affects logins/users for the user database(s). However, unlike
> operational databases, most data warehousing databases only have a limited
> number of users or profiles (ex: Admin and Reporting), so perhaps this
won't
> be too much of a deal.
> Rebuilding the master Database:
>
http://msdn.microsoft.com/library/d... />
t_0cvm.asp
in fact there are no particular users or profiles

> User logons and permissions on a database may be incorrect after the
> database is restored:
> http://support.microsoft.com/defaul...kb;en-us;168001
I see...

> As for restoring the Analysis Services cubes, I assume you at least have a
> backup of the development system which can be restored to production. OLAP
> data is typically just derived and aggregated, so once AS is re-installed
> and the cube files restored, just re-process and they will be re-loaded
from
> the source data warehouse tables.
> Microsoft SQL Server 2000 Analysis Services Operations Guide:
>
http://www.microsoft.com/technet/pr...n/anservog.mspx
>
sorry but I do not understand what you mean with a "backup of the
development system which can be restored to production".
As I wrote in my previous message I can have back the files tjat were in the
..\analysis service\* folder...
in any case thanks a lot for your answer, it is a great help!
ciao.sql

Disaster Recovery

We are trying to emulate a disk crash for one of our SQL servers but we are running into some difficulty. The working server is clustered but the server to restore to isn't. The databases (master, model, and msdb) are stored on e: on the working server bu
t are being restore to d: on the test server. The first database to restore be restored is master but after doing this the server will not restart with the error message "Unable to locate e:". What is the recommended procedure for doing this?
Thanks in advance,
-Dave
Hi
You MUST use SAME DISK CONFIGURATION, if you plan restore the master database!
The master database store file information for all databases, see sysdevices or sysdatabases table.
Andras Jakus MCDBA
"Dave" wrote:

> We are trying to emulate a disk crash for one of our SQL servers but we are running into some difficulty. The working server is clustered but the server to restore to isn't. The databases (master, model, and msdb) are stored on e: on the working server
but are being restore to d: on the test server. The first database to restore be restored is master but after doing this the server will not restart with the error message "Unable to locate e:". What is the recommended procedure for doing this?
> Thanks in advance,
> -Dave
|||If it's just a test environment, you could try subst e: d:\
Not 100% sure is SQL Server will recognize this when doing the restore, but
it would be worth a shot.
HTH,
Morgan
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:0212D0EE-852A-444B-8E73-5BF8446C3C21@.microsoft.com...
> We are trying to emulate a disk crash for one of our SQL servers but we
are running into some difficulty. The working server is clustered but the
server to restore to isn't. The databases (master, model, and msdb) are
stored on e: on the working server but are being restore to d: on the test
server. The first database to restore be restored is master but after doing
this the server will not restart with the error message "Unable to locate
e:". What is the recommended procedure for doing this?
> Thanks in advance,
> -Dave

Disaster Recovery

We are trying to emulate a disk crash for one of our SQL servers but we are
running into some difficulty. The working server is clustered but the server
to restore to isn't. The databases (master, model, and msdb) are stored on
e: on the working server bu
t are being restore to d: on the test server. The first database to restore
be restored is master but after doing this the server will not restart with
the error message "Unable to locate e:". What is the recommended procedure f
or doing this?
Thanks in advance,
-DaveHi
You MUST use SAME DISK CONFIGURATION, if you plan restore the master databas
e!
The master database store file information for all databases, see sysdevices
or sysdatabases table.
Andras Jakus MCDBA
"Dave" wrote:

> We are trying to emulate a disk crash for one of our SQL servers but we are runnin
g into some difficulty. The working server is clustered but the server to restore to
isn't. The databases (master, model, and msdb) are stored on e: on the working serv
er
but are being restore to d: on the test server. The first database to restore be restored is
master but after doing this the server will not restart with the error message "Unable to l
ocate e:". What is the recommended procedure for doing this?
> Thanks in advance,
> -Dave|||If it's just a test environment, you could try subst e: d:\
Not 100% sure is SQL Server will recognize this when doing the restore, but
it would be worth a shot.
HTH,
Morgan
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:0212D0EE-852A-444B-8E73-5BF8446C3C21@.microsoft.com...
> We are trying to emulate a disk crash for one of our SQL servers but we
are running into some difficulty. The working server is clustered but the
server to restore to isn't. The databases (master, model, and msdb) are
stored on e: on the working server but are being restore to d: on the test
server. The first database to restore be restored is master but after doing
this the server will not restart with the error message "Unable to locate
e:". What is the recommended procedure for doing this?
> Thanks in advance,
> -Dave