Attach DB with corrupt .ldf?

Hey gang,

I've been trying to restore a DB from it's MDF and LDF all morning;
the catch? The LDF appears to be corrupt.

When I first started the SQL Server, the db in question was marked as
"Suspect'. I did some research on this and it has caused me to attempt
detaching, backing up, deleting the LDF, using ATTACH DB and even
sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.

Nothing works.

So, with an MDF and no LDF ... is it possible to recreate this
database somehow? Can I attach the MDF to another (empty) database to
retrieve it's contents? (Tried it, couldn't get it to work), can I
extract the contents of the MDF - even if it's just the objects and
not the data itself - in some capacity?

I've read in various places about attaching an MDF with no LDF and the
system will recreate the LDF as needed, but that doesn't work either.

Ideas are most, most, most welcome.[posted and mailed]

NF (natalyafaden@.bellsouth.net) writes:
> I've been trying to restore a DB from it's MDF and LDF all morning;
> the catch? The LDF appears to be corrupt.
> When I first started the SQL Server, the db in question was marked as
> "Suspect'. I did some research on this and it has caused me to attempt
> detaching, backing up, deleting the LDF, using ATTACH DB and even
> sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.
> Nothing works.
> So, with an MDF and no LDF ... is it possible to recreate this
> database somehow? Can I attach the MDF to another (empty) database to
> retrieve it's contents? (Tried it, couldn't get it to work), can I
> extract the contents of the MDF - even if it's just the objects and
> not the data itself - in some capacity?
> I've read in various places about attaching an MDF with no LDF and the
> system will recreate the LDF as needed, but that doesn't work either.

There is a way, but I am not going to post it, because it's a
path too dangerous.

My first advice is that you open a case with Microsoft support. Yes,
that will cost you an arm and a leg, but consider how many arms and
legs losing the data will cost you. They have the procedures to recover
as much as possible.

My second advice is to restore from a clean backup.

The way I know means building a log from nothing and at all, and lead
you into the MDF as it was when things went bad. You may find a very
good database. You may also find a database that is just a big mess,
because you got it mid-transaction. And the fact that the LDF is corrupt
is an indication of that. Corruption may exist both in the SQL Server
structures and in your own data.

And if you don't understand the essence of what I'm saying above, don't
ask any further questions, but get on the phone with Microsoft.

If you absolutely want to do this on your own as a last resort before
you throw the database away, drop me a mail.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Attach db from installer (only read mode)

Hi,

I have made setup of my program,

I need to attach a db to the server. To do this:

- Copy mdf and ldf files into default data folder of instance of sqlexpress

- run scrip from custom action in the installer that:

a. attach the db

b. create login

c. create user for login in db

There is a problem...I run script slq file by sqlcmd and launch it from a c# process.

The rusult is that the db was attacched but in only read mode, and the point c not work.

If I execute manually the same script sql with sqlcmd in dos window, it work without problem in all point: a,b and c

What are the reason of this?

I think that start process in c# not work correctly....this is the code:

process = new Process();

process.StartInfo.FileName = "sqlcmd.exe";

process.StartInfo.Arguments = commandLine;

process.Start();

process.WaitForExit();

help me, plese!

thanks in advance

andrea

SInce you have posted this in Express forum, i strongly feel that you should use SQL Server Express User Instance feature. Its nothing but Embedded Database.

Refer this.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu

attach db file copied previously

I got a mdf file from my co-worker
who just stopped SQL server and delete ldf file
and give me a mdf file.
if it was detached, it would work fine
but it was just copied.

Is there any way to attach this mdf file?
I tried sp_attach_db and sp_attach_single_file_db
and both failed.

thanks,second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311241337.7338362d@.posting.google.com>...
> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_single_file_db
> and both failed.
> thanks,

If sp_attach_single_file_db failed, then there is no other way to
attach the file. In future, it would be better to ask your colleague
to give you a backup of the database, not the database file(s). This
also means your colleague will not have to stop SQL Server.

Simon|||What error do you get back from the sp_attach_single_file_db command?

> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_single_file_db
> and both failed.
> thanks,

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index...epartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||I ran
sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
and got

Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'd:\Spectrum\Data\Spectrum_log.LDF' may be incorrect.

Neil Pike <neilpike@.compuserve.com> wrote in message news:<VA.00001010.008366e0@.compuserve.com>...
> What error do you get back from the sp_attach_single_file_db command?
> > I got a mdf file from my co-worker
> > who just stopped SQL server and delete ldf file
> > and give me a mdf file.
> > if it was detached, it would work fine
> > but it was just copied.
> > Is there any way to attach this mdf file?
> > I tried sp_attach_db and sp_attach_single_file_db
> > and both failed.
> > thanks,
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or http://www.ntfaq.com/Articles/Index...epartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq|||[posted and mailed, please reply in news]

neo (second714@.hotmail.com) writes:
>> > I got a mdf file from my co-worker
>> > who just stopped SQL server and delete ldf file
>> > and give me a mdf file.
>> > if it was detached, it would work fine
>> > but it was just copied.
> I ran
> sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
> and got
> Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'd:\Spectrum\Data\Spectrum_log.LDF' may be incorrect.

Tell your co-worker to never do that again! Deleting LDF files is a
very stupid thing to do!

It is not clear how you co-worker shut down SQL Server, but it does
not seem that this database was shut down cleanly. If this database
was in the midst of the transaction, this transaction needs to be
rolled back, to bring the database to a consistent state. But to
roll back the transaction, you need the transaction log.

Yes, there *are* ways to attach a single MDF file in such a situation,
but these ways are not documented with a very good reason. It follows
from the previous paragraph that the database you get might be grossly
corrupt. On the other hand, if there was no activity in the database
at the time of the shutdown,

If you have a backup of this database, I suggest that you restore it.
If the database contains data which is not on a backup, and you really
need to save as much as possible, I would suggest that you open a
case with Microsoft. That is not going to be cheap, but neither is it
cheap to lose the data.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

attach databases without detaching

If i have all the mdf and ldf files that comprise of a database, is it
guaranteed that i can always attach the db even if they are not successfully
detached
The only ways i know of a successful attach are if the databases are
detached successfully and if SQL is gracefully shut down.
There are so many 3rd party tools that replicate these files asynchronously
or have these files mirrored on a bit level,etc... and then they just stop
the replication or mirroring and claim they can attach it... Will this work
all the time ?
Or lets put it this way... Under what conditions will attach not work even
if i have all the relevant data and log files
If SQL Server has been shut down and the files copied or they were dettached
properly there shouldn't be too much of a problem. But if they simply
attempt to copy the files while SQL Server is running there is not telling
what they will get.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>
|||If you did not detach the database, there is no guarantee that an attach
will always work, even if you shut down the database and copy the data and
log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>
|||echoing Andy and Peter, if you have multiple log files for a single DB it is
almost guranteed that an attach will not work without a prior detach.
"Peter Yeoh" wrote:

> If you did not detach the database, there is no guarantee that an attach
> will always work, even if you shut down the database and copy the data and
> log files.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> successfully
> asynchronously
> work
>
>
sql

attach databases without detaching

If i have all the mdf and ldf files that comprise of a database, is it
guaranteed that i can always attach the db even if they are not successfully
detached
The only ways i know of a successful attach are if the databases are
detached successfully and if SQL is gracefully shut down.
There are so many 3rd party tools that replicate these files asynchronously
or have these files mirrored on a bit level,etc... and then they just stop
the replication or mirroring and claim they can attach it... Will this work
all the time ?
Or lets put it this way... Under what conditions will attach not work even
if i have all the relevant data and log filesIf SQL Server has been shut down and the files copied or they were dettached
properly there shouldn't be too much of a problem. But if they simply
attempt to copy the files while SQL Server is running there is not telling
what they will get.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||If you did not detach the database, there is no guarantee that an attach
will always work, even if you shut down the database and copy the data and
log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||echoing Andy and Peter, if you have multiple log files for a single DB it is
almost guranteed that an attach will not work without a prior detach.
"Peter Yeoh" wrote:
> If you did not detach the database, there is no guarantee that an attach
> will always work, even if you shut down the database and copy the data and
> log files.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> > If i have all the mdf and ldf files that comprise of a database, is it
> > guaranteed that i can always attach the db even if they are not
> successfully
> > detached
> >
> > The only ways i know of a successful attach are if the databases are
> > detached successfully and if SQL is gracefully shut down.
> >
> > There are so many 3rd party tools that replicate these files
> asynchronously
> > or have these files mirrored on a bit level,etc... and then they just stop
> > the replication or mirroring and claim they can attach it... Will this
> work
> > all the time ?
> >
> > Or lets put it this way... Under what conditions will attach not work even
> > if i have all the relevant data and log files
> >
> >
>
>

attach databases without detaching

If i have all the mdf and ldf files that comprise of a database, is it
guaranteed that i can always attach the db even if they are not successfully
detached
The only ways i know of a successful attach are if the databases are
detached successfully and if SQL is gracefully shut down.
There are so many 3rd party tools that replicate these files asynchronously
or have these files mirrored on a bit level,etc... and then they just stop
the replication or mirroring and claim they can attach it... Will this work
all the time ?
Or lets put it this way... Under what conditions will attach not work even
if i have all the relevant data and log filesIf SQL Server has been shut down and the files copied or they were dettached
properly there shouldn't be too much of a problem. But if they simply
attempt to copy the files while SQL Server is running there is not telling
what they will get.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||If you did not detach the database, there is no guarantee that an attach
will always work, even if you shut down the database and copy the data and
log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||echoing Andy and Peter, if you have multiple log files for a single DB it is
almost guranteed that an attach will not work without a prior detach.
"Peter Yeoh" wrote:

> If you did not detach the database, there is no guarantee that an attach
> will always work, even if you shut down the database and copy the data and
> log files.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> successfully
> asynchronously
> work
>
>

Attach database problem

Hi Folks
I am a newbie trying to attach an MSDE dbf from a whatever.mdf and
whatever.ldf files. I keep getting an error that I can't attach the database
to the same name as an existing database.
Any help would be appreciated?
Danny
Hello,
Looks like the database name you specify in the atatch database is already
in the same server instance. Execute SP_HELPDB and make sure that
the database you specy is already not there.
Thanks
Hari
"Danny" <lane.dj@.gmail.com> wrote in message
news:OZdKYqZaHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Hi Folks
> I am a newbie trying to attach an MSDE dbf from a whatever.mdf and
> whatever.ldf files. I keep getting an error that I can't attach the
> database to the same name as an existing database.
> Any help would be appreciated?
> Danny
>

attach Database Error -very URGENT

hi to all
I got a probelm with attaching the database. I hv Database Name called "ABC". it is working fine. (contain MDF,LDF files)

I got new BackFiles from CLient. They sent MDF and LDF Files. But i could't able to attach the database i got the following error while attaching the databse.

Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.

I tried with the following query

EXEC sp_attach_db @.dbname = N'ABC',
@.filename1 = N'C:\DataBase\ABC.mdf',
@.filename2 = N'C:\DataBase\ABC_log.ldf'

i Tried with EnterPrise Manager also i got the same error. you can see the attachment file to view the ERROR.

Please Help me .Urgently...http://support.microsoft.com/default.aspx?scid=kb;EN-US;281122 to resolve the issue.

Attach Database

in the Enterprise Manager when one goes to attach a database it lets
you choose an MDF / LDF file. It then verifies the validity of the
database before attaching. (I also assume that it detects if the
database was spanning multiple files). I am wondering if there is a
sproc/sprocs that can be called to do this or is this done via
proprietary knowledge of the format that the mdf/ldf files are stored
inThe attaching or the check on spanning multiple files?
Attaching: sp_attach_db or sp_attach_single_file_db
Check for files: this is included in the header of the .mdf, primary data
file. When the database comes online, this information is populated into th
e
virtual sysfiles.
Sincerely,
Anthony Thomas
"johndoe@.driver.net" wrote:

> in the Enterprise Manager when one goes to attach a database it lets
> you choose an MDF / LDF file. It then verifies the validity of the
> database before attaching. (I also assume that it detects if the
> database was spanning multiple files). I am wondering if there is a
> sproc/sprocs that can be called to do this or is this done via
> proprietary knowledge of the format that the mdf/ldf files are stored
> in
>|||So If i wanted to see all the files attached to a database I would need to
read the file myself and parse the header?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> The attaching or the check on spanning multiple files?
> Attaching: sp_attach_db or sp_attach_single_file_db
> Check for files: this is included in the header of the .mdf, primary data
> file. When the database comes online, this information is populated into
the
> virtual sysfiles.
> Sincerely,
>
> Anthony Thomas
>
> "johndoe@.driver.net" wrote:
>|||Not too sure what the issue is.
If the database is online, then sp_helpdb <database name> will list out all
of the files used by the database.
If the database is detached and all you have is the primary data file, you
could try to use sp_attach_single_file_db and read the error messages as the
y
come up. This will tell you what the system is looking for.
However, if you or someone else has detached a database and you or someone
else didn't know what files belonged, shame on you. You should have checked
before you detached.
Sincerely,
Anthony Thomas
"johndoe@.community.nospam" wrote:
[vbcol=seagreen]
> So If i wanted to see all the files attached to a database I would need to
> read the file myself and parse the header?
> "AnthonyThomas" wrote:
>

Attach Database

in the Enterprise Manager when one goes to attach a database it lets
you choose an MDF / LDF file. It then verifies the validity of the
database before attaching. (I also assume that it detects if the
database was spanning multiple files). I am wondering if there is a
sproc/sprocs that can be called to do this or is this done via
proprietary knowledge of the format that the mdf/ldf files are stored
inThe attaching or the check on spanning multiple files?
Attaching: sp_attach_db or sp_attach_single_file_db
Check for files: this is included in the header of the .mdf, primary data
file. When the database comes online, this information is populated into the
virtual sysfiles.
Sincerely,
Anthony Thomas
"johndoe@.driver.net" wrote:
> in the Enterprise Manager when one goes to attach a database it lets
> you choose an MDF / LDF file. It then verifies the validity of the
> database before attaching. (I also assume that it detects if the
> database was spanning multiple files). I am wondering if there is a
> sproc/sprocs that can be called to do this or is this done via
> proprietary knowledge of the format that the mdf/ldf files are stored
> in
>|||So If i wanted to see all the files attached to a database I would need to
read the file myself and parse the header?
"AnthonyThomas" wrote:
> The attaching or the check on spanning multiple files?
> Attaching: sp_attach_db or sp_attach_single_file_db
> Check for files: this is included in the header of the .mdf, primary data
> file. When the database comes online, this information is populated into the
> virtual sysfiles.
> Sincerely,
>
> Anthony Thomas
>
> "johndoe@.driver.net" wrote:
> > in the Enterprise Manager when one goes to attach a database it lets
> > you choose an MDF / LDF file. It then verifies the validity of the
> > database before attaching. (I also assume that it detects if the
> > database was spanning multiple files). I am wondering if there is a
> > sproc/sprocs that can be called to do this or is this done via
> > proprietary knowledge of the format that the mdf/ldf files are stored
> > in
> >|||Not too sure what the issue is.
If the database is online, then sp_helpdb <database name> will list out all
of the files used by the database.
If the database is detached and all you have is the primary data file, you
could try to use sp_attach_single_file_db and read the error messages as they
come up. This will tell you what the system is looking for.
However, if you or someone else has detached a database and you or someone
else didn't know what files belonged, shame on you. You should have checked
before you detached.
Sincerely,
Anthony Thomas
"johndoe@.community.nospam" wrote:
> So If i wanted to see all the files attached to a database I would need to
> read the file myself and parse the header?
> "AnthonyThomas" wrote:
> > The attaching or the check on spanning multiple files?
> >
> > Attaching: sp_attach_db or sp_attach_single_file_db
> >
> > Check for files: this is included in the header of the .mdf, primary data
> > file. When the database comes online, this information is populated into the
> > virtual sysfiles.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "johndoe@.driver.net" wrote:
> >
> > > in the Enterprise Manager when one goes to attach a database it lets
> > > you choose an MDF / LDF file. It then verifies the validity of the
> > > database before attaching. (I also assume that it detects if the
> > > database was spanning multiple files). I am wondering if there is a
> > > sproc/sprocs that can be called to do this or is this done via
> > > proprietary knowledge of the format that the mdf/ldf files are stored
> > > in
> > >

Attach Database

in the Enterprise Manager when one goes to attach a database it lets
you choose an MDF / LDF file. It then verifies the validity of the
database before attaching. (I also assume that it detects if the
database was spanning multiple files). I am wondering if there is a
sproc/sprocs that can be called to do this or is this done via
proprietary knowledge of the format that the mdf/ldf files are stored
in
The attaching or the check on spanning multiple files?
Attaching: sp_attach_db or sp_attach_single_file_db
Check for files: this is included in the header of the .mdf, primary data
file. When the database comes online, this information is populated into the
virtual sysfiles.
Sincerely,
Anthony Thomas
"johndoe@.driver.net" wrote:

> in the Enterprise Manager when one goes to attach a database it lets
> you choose an MDF / LDF file. It then verifies the validity of the
> database before attaching. (I also assume that it detects if the
> database was spanning multiple files). I am wondering if there is a
> sproc/sprocs that can be called to do this or is this done via
> proprietary knowledge of the format that the mdf/ldf files are stored
> in
>
|||So If i wanted to see all the files attached to a database I would need to
read the file myself and parse the header?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> The attaching or the check on spanning multiple files?
> Attaching: sp_attach_db or sp_attach_single_file_db
> Check for files: this is included in the header of the .mdf, primary data
> file. When the database comes online, this information is populated into the
> virtual sysfiles.
> Sincerely,
>
> Anthony Thomas
>
> "johndoe@.driver.net" wrote:
|||Not too sure what the issue is.
If the database is online, then sp_helpdb <database name> will list out all
of the files used by the database.
If the database is detached and all you have is the primary data file, you
could try to use sp_attach_single_file_db and read the error messages as they
come up. This will tell you what the system is looking for.
However, if you or someone else has detached a database and you or someone
else didn't know what files belonged, shame on you. You should have checked
before you detached.
Sincerely,
Anthony Thomas
"johndoe@.community.nospam" wrote:
[vbcol=seagreen]
> So If i wanted to see all the files attached to a database I would need to
> read the file myself and parse the header?
> "AnthonyThomas" wrote:

Attach command failes

Hi
I have just received data files ( .mdf and .ldf ) and I need to attach them
to a sql express 2005. My machine is WinXP vers. 2002 SP2.
I downloaded the varius files (.NET 2, sql 2005, sql management studio
express) and installation was ok. No errors. No warnings
I am trying now to attach the data from management studio console (Database
- Attach).
But when adding .mdf file location I receive the following message:
"Failed to retrieve data.. CREATE FILE encounted operating system error 5
while attempting to open/create... Error 5123"
Do you miss anything? Any idea?
Thank you for your suggestions.Francesco wrote:
> Hi
> I have just received data files ( .mdf and .ldf ) and I need to attach them
> to a sql express 2005. My machine is WinXP vers. 2002 SP2.
> I downloaded the varius files (.NET 2, sql 2005, sql management studio
> express) and installation was ok. No errors. No warnings
> I am trying now to attach the data from management studio console (Database
> - Attach).
> But when adding .mdf file location I receive the following message:
> "Failed to retrieve data.. CREATE FILE encounted operating system error 5
> while attempting to open/create... Error 5123"
> Do you miss anything? Any idea?
> Thank you for your suggestions.
Google says this is "access denied":
operating system error 5 !!!|||On Feb 14, 8:01=A0am, Zarko Jovanovic
<mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote:
> Francesco wrote:
> > Hi
> > I have just received data files ( .mdf and .ldf ) and I need to attach t=hem
> > to a sql express 2005. My machine is WinXP vers. 2002 SP2.
> > I downloaded the varius files (.NET 2, sql 2005, sql management studio
> > express) and installation was ok. No errors. No warnings
> > I am trying now to attach the data from management studio console (Datab=ase
> > - Attach).
> > But when adding .mdf file location I receive the following message:
> > "Failed to retrieve data.. CREATE FILE encounted operating system error =5
> > while attempting to open/create... Error 5123"
> > Do you miss anything? Any idea?
> > Thank you for your =A0suggestions.
> Google says this is "access denied":
> operating system error 5 !!!- Hide quoted text -
> - Show quoted text -
To explain, SQL Management Studio is trying to look for a file where
you do not have permissions. This can be caused by (simple) NT file
permissions or (not so simple) something else is holding the .mdf
file. The latter problem is usually some type of anti-virus software
trying to scan the .mdf and .ldf files.
If you've double-checked the directory permissions, you may want to
use Sysinternals Process Explorer (which is a free download from
Microsoft) and search for handles on the .mdf - that will tell you if
something else strange is going on.|||Make sure SQL Management Studio is running as admin. Setting full
permissions on the database file alone wasn't sufficient for me. Sign on as
admin in Win XP. If using Vista, right click on the SQL Studio application
link and select Run as Administrator. Then you will be able to attach a
database from anywhere in the file system.

attach a database

I am trying to attach a database , using sp_attach_db command, that has one
MDF file and one LDF file. The LDF file was corrupt so that did not work.
I then I tried the sp_attach_single_file_db command, I got an error could no
t open the MDF file.
Any ideas on what the problem is and how to proceed?
Thanks a lot in advanceIn order to attach a database, you need to have detached it first. It is
possible that your mdf and ldf files were just copied over after the SQL
Server service was stopped. If that was the case, you could try the steps
provided by Jasper Smith at http://tinyurl.com/2o9w8
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Daneil Hope" <anonymous@.discussions.microsoft.com> wrote in message
news:CA65F4FA-FF0E-4DC2-88C6-E7A7D1599646@.microsoft.com...
> I am trying to attach a database , using sp_attach_db command, that has
one MDF file and one LDF file. The LDF file was corrupt so that did not
work. I then I tried the sp_attach_single_file_db command, I got an error
could not open the MDF file.
> Any ideas on what the problem is and how to proceed?
> Thanks a lot in advance|||Thanks a lot Peter. I will try that.

attach a database

I am trying to attach a database , using sp_attach_db command, that has one MDF file and one LDF file. The LDF file was corrupt so that did not work. I then I tried the sp_attach_single_file_db command, I got an error could not open the MDF file.
Any ideas on what the problem is and how to proceed?
Thanks a lot in advance
In order to attach a database, you need to have detached it first. It is
possible that your mdf and ldf files were just copied over after the SQL
Server service was stopped. If that was the case, you could try the steps
provided by Jasper Smith at http://tinyurl.com/2o9w8
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Daneil Hope" <anonymous@.discussions.microsoft.com> wrote in message
news:CA65F4FA-FF0E-4DC2-88C6-E7A7D1599646@.microsoft.com...
> I am trying to attach a database , using sp_attach_db command, that has
one MDF file and one LDF file. The LDF file was corrupt so that did not
work. I then I tried the sp_attach_single_file_db command, I got an error
could not open the MDF file.
> Any ideas on what the problem is and how to proceed?
> Thanks a lot in advance
|||Thanks a lot Peter. I will try that.

attach Help

I received three Files .mdf .ldf .ndf and a text how to use attach
but i always receive an error message "database is write protect ......"
What I do wrong ?
byeFirst of all, what did you do?|||Are the three files read-only at the O/S level? This can happen if the three files were sent as attachments in e-mail.|||could it be that the database already exists and in read-only mode?|||Are we even talking about SQL Server?

And when does the damn weekend start already.....

DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 16 2004 17:00:00'
SELECT DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margaritaville|||Sorry for mistakes
I'll start again
i have 3 Files xxx.mdf,xxx.ndf,xxx.ldf from originally sql7 ,from network and I'd like to atach to Sqlserver 2000; on sql server 2000 is the database not present
With Query Analyzer I tested i did:
Exec sp_attach_db
@.dbname="xxx",
@.filename1= "xxx.mdf",
@.filename2= "xxx.ndf",
@.filename3= "xxx.ldf"

With Sql 2000 Manager I use The menu attach

In both case i received this message "The Database is write Protect you
have to unptrotect.......

bye|||Right click on each file in succession, go to properties, and see if any of them have the read-only checkbox marked. If so, uncheck that box.|||I checked the free file , they all are not write protect|||As a test, see if you can rename one of the files. Maybe backup software, or virus scan has it open?

I just tried a test of re-attaching a readonly filegroup, and it worked. Although, I do not have access to a 7.0 machine, anymore to try out a readonly filegroup with an upgrade.

That sparks a thought. Do you have access to the database in it's original state? That is, can you see if the original database (on SQL 7.0) is read only or had read only filegroups?|||I can rename the files, and I open the file .ndf with an Hex editor
there are more line with read only inside the file

bye

Associating mdf and ldf files to databases

Is there some sort of command that can tell me which mdf and ldf files are associated to which databases?

Thanks,
Ninel

There are many ways to get the information depending on the version of SQL Server. You can query the following tables/views:

master.dbo.sysaltfiles (SQL Server 2000)

sys.master_files (SQL Server 2005)

To look at individual databases only, you can use the system stored procedure "sp_helpdb" in any version of SQL Server.