November 13, 2003 at 9:35 am
I really did it now. In attempting to copy over a SQL database, I inadvertently copied the wrong LDF log file. Now, I can't attach to the database. Is there anyway I can attach to my MDF file without the LDF file? I tried to use sp_attach_single_db_file, but it says that it can not activate some files (refering to the LDF file that is missing). Alternatively, can I force the creation of an LDF file that will be in sync with the MDF file I have? I'm worried I might be screwed here - tell me it isn't so!
November 13, 2003 at 9:51 am
if you haven't detached the database from the target server (so that it doesn't exist on the target server ) , i don't think sp_attach_single_file_db will work.
Paul
November 13, 2003 at 9:55 am
Paul,
It doesn't work, and unfortunately I can't correctly detach it since it does not show up as existing at this point.
November 13, 2003 at 9:57 am
can you not attach it as a different name ?
November 13, 2003 at 10:01 am
I just tried it, but it came back with the same error (...some of the files cannot be attached) since there is no LDF file.
November 13, 2003 at 10:03 am
or could you not create an empty database, and copy the log file to the location of the incorrect log ? This may trick the server into thinking that the log is there (although empty) and it may recover.
November 13, 2003 at 10:13 am
Have you tried to attach from the Enterprise Manager.
I have had these problem one day. And from the Enterprise Manager I attached the database correctly.
You will only get a warning message saying that don't find the .ldf file and will create a new LDF.
If it doesn't work, check that the path is well spell.
November 13, 2003 at 10:21 am
Okay, just tried that and I get an error message that 'cannot associate files from different databases'
November 13, 2003 at 10:38 am
petpet
Attach is grayed out on Enterprise Manager. I'm assuming it is because this particular database is in MS SQL 7.
November 13, 2003 at 12:51 pm
Before you do anything else make complete backups of every database on that server that you can especially master and msdb.
Have you tried creating a dummy file of the same name and location as the log file? Just an empty text file maybe?
I'm not sure why attaching the single data file isn't working though. Are you trying to reattach to the same database server?
You could try detaching all other databases and rebuild your master database. Then re-attach all of them.
I don't recommend this but if you get really desperate you could set the server to allow updates to the system tables and modify the data file path directly. Make sure to back up all other databases including master first though.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
November 14, 2003 at 4:27 am
If everything else fails, try MSSQLRecovery tool. I accidentally found this tool, but when I really needed it I didn't know about it. Maybe it will be just right for you. Here is some description and home page:
MSSQLRecovery is data recovery program for Microsoft SQL Server databases and backups (.mdf, .dat).
MSSQLRecovery has a powerful engine capable of retrieving data from most damaged databases. Tables, stored procedures, views, indexes and numerous other items are recovered
Edited by - jcool on 11/14/2003 04:35:24 AM
November 14, 2003 at 6:23 am
Before attempting any of this solutions below make sure you have done following
shutdown server , copy ur mdf to some other location (as a backup) & also have a master database backup & then Follow this sequence
assuming ur database is in mark suspect
A.
1. dbcc dbrepair ('dbname', DROPDB )
2. sp_attach_single_file_db dbname, 'physical path to ur mdf file'
3. restart SQL Server
B.
1. dbcc dbrepair ('dbname', DROPDB )
2. create a database by same name with same mdf & log file spec (you must have it).
3. stop SQL Server
4. overwrite newdb's mdf file with old one
5. restart SQL server
6. if required run sp_resetstatus to reset mark suspect status of database.
November 14, 2003 at 7:30 am
It's been a while since I used this but I think if you manually update the sysdatabases table and set the status field to 32768 (emergency mode) you will be able to read the data out of the database. You will have to restart the server after you change the status of the database.
November 17, 2003 at 12:45 pm
This will work !!!
Restoring databases when only data file available
•shutdown sql
•move the current database file or rename it
•restart sql server
•create a new database of the same name and log file and location as the old database and log file
•get rid of the old database(you may be able to right click delete it in this situation or used sp_removedb)
•create a new database of the right size and shape with correct log and data file locations
•stop sql
•rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf
•move back in the old database .mdf file or rename it back again
•restart sql server(it should come up suspect)
-------------------------------------------------------------------------------------------------------------------
•From a query window, set the status so that you can update the system tables by running
the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
•Then set the status of the DB that is giving you the problem (XXXXX) into Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
•Go into the data directory (MSSQL7\DATA) and rename the log file associated the DB in question (XXXX.ldf) to some temporary name, such as XXXX.TMP.
•Exit the query window.
•Start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
•Bring up another query window and verify that the DB is in emergency mode by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
•Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <--- You will need the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large database. It should complete with the message “DBCC execution completed”
•Take the database out of bypass recovery mode by issuing the command :
update sysdatabases set status = 0 where name = '<DBName>'
•Exit the query window and then shutdown (Ctrl-C in the DOS window) and
•restart SQL server.
•Verify the status of the database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
Edited by - cchitanu on 11/17/2003 12:47:00 PM
November 17, 2003 at 1:13 pm
Thanks! Back in business-
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply