Recovery from crash

Greetings,

I had a hard-drive failure this week and lost my root drive, with
operating system and SQL Server 2000.

Fortunately my data was on another volume and still exists.

I know about sp_attach_db, but looking at BOL, it says that sp_attach_db
only works with databases that were previously detached with
sp_detach_db.

How do I reattach my existing mdf and ldf files?

-- Rick

P.S. I know that the dbs are not corrupt because when the hard drive
failed SQL Server was offline.Guinness Mann (GMann@.dublin.com) writes:
> I had a hard-drive failure this week and lost my root drive, with
> operating system and SQL Server 2000.
> Fortunately my data was on another volume and still exists.
> I know about sp_attach_db, but looking at BOL, it says that sp_attach_db
> only works with databases that were previously detached with
> sp_detach_db.
> How do I reattach my existing mdf and ldf files?
> P.S. I know that the dbs are not corrupt because when the hard drive
> failed SQL Server was offline.

I would first try with sp_attach_db. If SQL Server was shut down cleanly,
I would expect the database files to be available.

If that fails, I would try sp_attach_single_file_db.

If that fails, there is an undocumented and unsupported method, that
I am not telling about in a newsgroup, because that methods is just
simply far too dangerous. I have no problem sharing it by mail. But
please try sp_detach_db first.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <Xns93F5E2EFD74Yazorman@.127.0.0.1>, sommar@.algonet.se says...
> > I know about sp_attach_db, but looking at BOL, it says that sp_attach_db
> > only works with databases that were previously detached with
> > sp_detach_db.
> I would first try with sp_attach_db. If SQL Server was shut down cleanly,
> I would expect the database files to be available.

sp_attach_db worked great, Erland. Thank you.

-- Rick

recovery a SQL Server db from mdf/Ldf files

hi to all,

I'm newbie with Sql Server,

I've a serious problem. After a crash of one of my disk I loose all the data about the SQL Server db but I've the mdf and ldf files because were placed on another hard disk different from the SQL Serve Installation disk. I reinstalled the Sql Server (in a new path, a new hard disk letter) but I don't know how to rebuild the db starting from the mdf/ldf files. Is it possible?

Please help me, I'm in a ugly Situation.

If you can access the mdf/ldf files, simply doing an attach will do the trick ... sometimes|||

can you teach me how to attach the files? please help me, I need a solution for this problem

thanks a lot

Claudio

|||

What version are you using...SQL Server 2000 or 2005?

|||...I'm using SQL Server 2000 developer + sp3|||Open Books Online. Do a search for Attach. It will have instructions and sample code.

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

|||I got the same problem and the sames answers from 2000 and 2005. Any idea will help me too.|||This has to go back to your backups. Do you have full/differential/log backups? What's your disk architecture?|||I posted too fast...didn't get to read that you do not have backups...I have another question...did the entire server go down or only the disk?|||The whole drive failed. I was able to freeze it and get the mdf, but the log file was corrupt.

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

|||I got the same problem and the sames answers from 2000 and 2005. Any idea will help me too.|||This has to go back to your backups. Do you have full/differential/log backups? What's your disk architecture?|||I posted too fast...didn't get to read that you do not have backups...I have another question...did the entire server go down or only the disk?|||The whole drive failed. I was able to freeze it and get the mdf, but the log file was corrupt.

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

|||I got the same problem and the sames answers from 2000 and 2005. Any idea will help me too.|||This has to go back to your backups. Do you have full/differential/log backups? What's your disk architecture?|||I posted too fast...didn't get to read that you do not have backups...I have another question...did the entire server go down or only the disk?|||The whole drive failed. I was able to freeze it and get the mdf, but the log file was corrupt.

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don
Hi, Don! As long as the db files are not corrupt, you can try using the sp_attach_db after you reinstall. See BOL for details. HTH. =)
|||Prerequisite:
Copy all the MDF and LDF files (User databases and System databases) to a safe directory,
Installation:
Since the C drive is crashed, you have to Install the Windows NT 4.0 with the same service pack.
After that install SQL 7.0 with same directory structure and the same service pack as before.
Now follow the below steps:-
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the new installation) to a new folder
3. Copy back all the MDF and LDF files to the same old folders ( Files copied in prerequisite step)
4. Start the SQL server and check all the databases are accessible.
The above step will work fine if you have the system files safe.
If the above steps fail then you will have to restore from good backup files.
-- Don wrote: --
SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
DonHi, Don! As long as the db files are not corrupt, you can try using the sp_a
ttach_db after you reinstall. See BOL for details. HTH. =)|||Prerequisite:
Copy all the MDF and LDF files (User databases and System databases) to a sa
fe directory,
Installation:
Since the C drive is crashed, you have to Install the Windows NT 4.0 with th
e same service pack.
After that install SQL 7.0 with same directory structure and the same servic
e pack as before.
Now follow the below steps:-
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the n
ew installation) to a new folder
3. Copy back all the MDF and LDF files to the same old folders ( Files copie
d in prerequisite step)
4. Start the SQL server and check all the databases are accessible.
The above step will work fine if you have the system files safe.
If the above steps fail then you will have to restore from good backup files
.
-- Don wrote: --
SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
DonHi, Don! As long as the db files are not corrupt, you can try using the sp_attach_db after you reinstall. See BOL for details. HTH. =)|||Prerequisite
Copy all the MDF and LDF files (User databases and System databases) to a safe directory
Installation
Since the C drive is crashed, you have to Install the Windows NT 4.0 with the same service pack.
After that install SQL 7.0 with same directory structure and the same service pack as before
Now follow the below steps:
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the new installation) to a new folde
3. Copy back all the MDF and LDF files to the same old folders ( Files copied in prerequisite step
4. Start the SQL server and check all the databases are accessible
The above step will work fine if you have the system files safe
If the above steps fail then you will have to restore from good backup files
-- Don wrote: --
SQL 7.0/NT 4.
The C drive has crashed. The MDF and LDF files are on
Drive D
Is there any path to recovery
Thanks
Do

Recover Databases after OS Crash

WE had a OS Crash and now we have to recover the databases.
We dont have any backup after we installed SQL Server. But
we have now all the data files *.mdf & *.ldf for all
databases including system databases.
Is there a way to recover the user and system databases.
Appreciate any help.
Thanks,
VCHi,
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
MCDBA
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC|||You can try sp_attach_db using the .mdf and ldf files. That is not
guaranteed since you didn't detach them beforehand. The files could be
corrupted by the server crash. If the log file(s) corrupted, you can also
try sp_attach_single_file_db. The fact that you don't have backups implies
that these databases are not important. Maybe you can find a way to recreate
them?
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC

Recover Databases after OS Crash

WE had a OS Crash and now we have to recover the databases.
We dont have any backup after we installed SQL Server. But
we have now all the data files *.mdf & *.ldf for all
databases including system databases.
Is there a way to recover the user and system databases.
Appreciate any help.
Thanks,
VC
Hi,
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
MCDBA
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC
|||You can try sp_attach_db using the .mdf and ldf files. That is not
guaranteed since you didn't detach them beforehand. The files could be
corrupted by the server crash. If the log file(s) corrupted, you can also
try sp_attach_single_file_db. The fact that you don't have backups implies
that these databases are not important. Maybe you can find a way to recreate
them?
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC

Recover Databases after OS Crash

WE had a OS Crash and now we have to recover the databases.
We dont have any backup after we installed SQL Server. But
we have now all the data files *.mdf & *.ldf for all
databases including system databases.
Is there a way to recover the user and system databases.
Appreciate any help.
Thanks,
VCHi,
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
MCDBA
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC|||You can try sp_attach_db using the .mdf and ldf files. That is not
guaranteed since you didn't detach them beforehand. The files could be
corrupted by the server crash. If the log file(s) corrupted, you can also
try sp_attach_single_file_db. The fact that you don't have backups implies
that these databases are not important. Maybe you can find a way to recreate
them?
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC

Recover Analysis Server from crash recovery

I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape. My
server came on-line with no error, i've not detect lost data, but, I don't
know if AS is now all fonctional or i need some configurations for finish
recover. The books on-line from Microsoft site have not information about
crash recovery.
Please can you tell me if i need some other restore files or other
configurations on AS ?
Thanks.
Sylvain H.
You don't need anything else...
Bye
Francesco Anti
"SHuet" <SHuet@.discussions.microsoft.com> ha scritto nel messaggio
news:CEAE3231-6CCA-421B-AF82-D255ADFB3EA5@.microsoft.com...
> I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape.
> My
> server came on-line with no error, i've not detect lost data, but, I don't
> know if AS is now all fonctional or i need some configurations for finish
> recover. The books on-line from Microsoft site have not information about
> crash recovery.
> Please can you tell me if i need some other restore files or other
> configurations on AS ?
> Thanks.
> Sylvain H.
>

Recover Analysis Server from crash recovery

I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape. My
server came on-line with no error, i've not detect lost data, but, I don't
know if AS is now all fonctional or i need some configurations for finish
recover. The books on-line from Microsoft site have not information about
crash recovery.
Please can you tell me if i need some other restore files or other
configurations on AS ?
Thanks.
Sylvain H.You don't need anything else...
Bye
Francesco Anti
"SHuet" <SHuet@.discussions.microsoft.com> ha scritto nel messaggio
news:CEAE3231-6CCA-421B-AF82-D255ADFB3EA5@.microsoft.com...
> I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape.
> My
> server came on-line with no error, i've not detect lost data, but, I don't
> know if AS is now all fonctional or i need some configurations for finish
> recover. The books on-line from Microsoft site have not information about
> crash recovery.
> Please can you tell me if i need some other restore files or other
> configurations on AS ?
> Thanks.
> Sylvain H.
>