RE: How can I attach a SQL2000 database that only has .MDF file

RE: How can I attach a SQL2000 database that only has .MDF file

 

  

Hello All,
My damaged database finally got attached based on Shashi's suggestion and Piyush's link.
Actually, the database was not detached clearly. Originally we appended data to a big table in the database from a another server. This appending generated a hug .LDF and used up all the free space on the disk even we set the recover model to be SIMPLE. The transaction was hanged. We clicked cancel of the DTS. The database was not shrunk automatically and manual shrink won't help. We have no other choice except to detach the database. We detached it. But we got error message said can't detach. The database was not on the EM any more. Then we deleted the LDF file to free the space. Since then, we can't attach the MDF file. Both sp_attach_db and sp_attach_sigle_file_db won't work. Neither GUI nor QA.
I almost give up until got Shashi's email to create a empty database and change the name. Stop, restart the Service. At least I can see the database on EM , but it was suspended. Then I use DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')
to regenerate the Log file.
By the way, the empty database created doesn't not have to be the same size.

Thank you all for all your inputs, especially, Shashi, Rick, and Piyush
This room is really a big help for me.


JIANMING
________________________________

From: Shashi P. Bushan [mailto:mssqldba-ezmlmshield-x68917194.[Email address protected]
Sent: Mon 1/29/2007 11:29 PM
To: LazyDBA Discussion
Subject: RE: How can I attach a SQL2000 database that only has .MDF file



If you got only primary data file, all secondary files and log files are
lost
sp_attach_db doesn't work because it looks for all files that
originally comprised db
a) backup the file before going any further
b) rename the file to filename_old
c) create new db that has one file of the same physical name
and size as our file
(logical name of the file and name of db can be different
from original)
d) stop Sql server
e) replace original file for mdf file of newly created db
f) start SQL server
g) in SQL Server logs are messages 'Log file does not match the
primary file.',
'Cannot associate files with different databases.' and db is
suspect
h) Set database in emergency mode:

sp_configure 'allow updates', 1
reconfigure with override
go
update sysdatabases
set status = 32768
where name = 'db_name'
go
sp_configure 'allow updates', 0
reconfigure with override
go
i) Now Data should by accessible - Export data out of the
database using bcp or DTS
j) drop db created in step c


Guess this will resolve the issue....

-Shashi

-----Original Message-----
From: Faried Lagardien
[mailto:mssqldba-ezmlmshield-x52053342.[Email address protected]
Sent: Tuesday, January 30, 2007 10:56 AM
To: LazyDBA Discussion
Subject: RE: How can I attach a SQL2000 database that only has .MDF file

Xing,

If you're using EM, you can attach a database without the logfile.

In your case, do you have a logfile or not? If you do, and you cannot
use it
to attach, then delete the file or move it to another location.

Then, when you attempt to attach the MDF, you'll be prompted that a new
LDF
will be created for you. Click yes and you're done...

-----Original Message-----
From: Michael Sexton (US-IT Contractor)
[mailto:mssqldba-ezmlmshield-x5550580.[Email address protected]
Sent: Monday, January 29, 2007 21:11
To: LazyDBA Discussion
Subject: RE: How can I attach a SQL2000 database that only has .MDF file

Use sp_attach_single_file_db

Mike

-----Original Message-----
From: Jianming Xing
[mailto:mssqldba-ezmlmshield-x46178493.[Email address protected]
Sent: Monday, January 29, 2007 11:56 AM
To: LazyDBA Discussion
Subject: How can I attach a SQL2000 database that only has .MDF file

Hello all,
I have a detached database that has only .MDF file. When I try to attach
it, I got Error 1813. It said "Could not open new database 'XXXX'.
Create database is aborted. Device activation error. The physical file
name XXXX.LDF may be incorrect". We should be able attach a database
that has no .LDF. It supposes to create a new .LDF. But I failed.
Does any pro have experience on attaching only .MDF database?
thank you in advance for any tips.


JIANMING


This e-mail message and any attachments are confidential. They may also
be privileged or otherwise protected by work product immunity. If you
have received this e-mail by mistake, please notify us by reply e-mail,
delete it from your system, and any disclosure, copying or use of its
contents is prohibited. Thank you, www.oceantomo.com.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




MS Sql Server LazyDBA home page