SQL Server Performance Forum – Threads Archive

Lose log file

Hi everyone, How can I use a database whose log file is lost? Thanks

Hi, dont sure but using sp_attach_single_file_db you can do it, it will make a clean and new log file for you while attaching your db. BOL says what should be taken care while using this option : <blockquote id="quote">quote:<hr height="1" noshade id="quote">Remarks When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database. Use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation. Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.<hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

quote:Originally posted by vietcave
How can I use a database whose log file is lost?

do you have any clue of how did it lose, bcos you will not be able to attach it from sp_attach_single_file_db if it is not detached —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

No, my hard disk containing the log file is formated.

hi,
Log file is formated ?! what does it mean ? is your log file get currepted ?! Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Another way might be to use dbcc rebuild_log as described here:http://www.windowsitpro.com/SQLServer/Article/ArticleID/8636/8636.html
Make sure you read closely through the explanations at the end of the chapter "Shrinking an Active Log File in SQL Server 7.0".
However, the question is still valid. How have you lost the t-log file? Or is this a hypothetical scenario question? Anyway, that’s a good example where a sound backup and disaster recovery plan is essential. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

I reinstall my Windows. I did not notice my log file is in hard disk D. I formated D before installing Windows. So I lost my log file. Regards

Oops, when I started typing there wasn’t your explanation how this has happened. So, someone did a low-level formatting of the HD? Might want to visit him with a big sledgehammer? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Well, in this case am I guessing right that you didn’t properly take the database offline?
Chances are more than good that this action corrupted your database, so that you are ot able to recover. You have a recent backup, haven’t you?
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hi, <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by vietcave I reinstall my Windows. I did not notice my log file is in hard disk D. I formated D before installing Windows. So I lost my log file. Regards <hr height="1" noshade id="quote"></blockquote id="quote"> hi, may give a try to suggested sp_attach_single_db or create a new database with same name and physical structure e.g. the location of .mdf and .ldf files and physical and logical name. then de-attach the db , stop sql service and then replace copy your existing db’s .mdf file with newly created db’s (with same name) .mdf file. this is just suggestion might get succeed. Any suggestions on this Frank / Satya, is this possible ! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

My backup also is in hard disk D. I wish I had it.

Hi,
To recover lost / formated data you might try with Stellar Phonix Tool tooo , have you tried my suggested way ! is it working ?! Regards
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

I haven’t tried this myself yet. However, what I have once tried was to copy and burn an online database on CD, Well, I got it on CD, but I wasn’t able to bring it back online again. I even looked at the files with a hex editor, but didn’t succeed. But it’s worth a try. Especially when you don’t have a recent backup. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

sp_attach_single_db will not work as you didn;’t detached it —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

Exactly, sp_attach_single_file doesn’t work well. I have an error 1813. I created a new database that is the same name as my old database. And then I replace these data files. But it still doesn’t work. It seems data file and log file have a contraint about time.

quote:Originally posted by vietcave Exactly, sp_attach_single_file doesn’t work well. I have an error 1813. I created a new database that is the same name as my old database. And then I replace these data files. But it still doesn’t work. It seems data file and log file have a contraint about time.

is it that you have more then one data file ?! and have you created new db with same name and same no of files ?! Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

No, my database has one data file. I create a new database whose name is the same as my old database. And then I shutdown my SQL Server. I delete the data file of this new database (but don’t delete log file) and copy the data file of my old database to replace. It doesn’t work…

Hi, <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by vietcave No, my database has one data file. I create a new database whose name is the same as my old database. And then I shutdown my SQL Server. I delete the data file of this new database (but don’t delete log file) and copy the data file of my old database to replace. It doesn’t work… <hr height="1" noshade id="quote"></blockquote id="quote"> please re-do again , don’t delete newly created .mdf file and copy on it which will overwrite existing new .mdf with old .mdf file. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Don’t delete old .mdf<hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

What a pity! I still can not access it. I check properties by right clicking my old and new data file. And I find out that their times are different. Maybe it is the reason that prevents me from recovering my database. Thank you very much

Try the following process, which I’ve documented and followed in critical cases at my end REBUILD LOG and recover a suspect database: 1. Back up the .mdf/.ndf and .ldf files. 2. Change the database context to Master and allow updates to system tables: <pre id="code"> Use Master Go sp_configure ‘allow updates’, 1 reconfigure with override Go </pre id="code"> 3. Set the database in Emergency (bypass recovery) mode: <pre id="code"> select * from sysdatabases where name = ‘<db_name>' — note the value of the status column for later use begin tran update sysdatabases set status = 32768 where name = ‘<db_name>' — Verify one row is updated before committing commit tran </pre id="code"> If you run DBCC REBUILD_LOG without setting the database in Emergency mode, the command does not work. You do not receive an error, but the log is not rebuilt either. 4. Stop and restart SQL server. 5. The syntax for DBCC REBUILD_LOG is as follows: <pre id="code"> DBCC REBUILD_LOG(‘<db_name>’,’<log_filename>’)</pre id="code"> If a log file with the same name as specified in DBCC REBUILD_LOG already exists in that directory, then the following message occurs: Server: Msg 5025, Level 16, State 1, Line 1 The file ‘C:MSSQL7Data<log_filename>’ already exists. You will need to specify a different filename or rename or delete the existing one. Rebuild the log with this code: <pre id="code"> DBCC TRACEON (3604) DBCC REBUILD_LOG(‘<db_name>’,’<log_filename>’) Go </pre id="code"> If the command is successful, the following message appears: Warning: The log for database ‘<db_name>’ has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted. You must reset the status using sp_dboption or through Enterprise Manager. 6. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency: sp_dboption ‘<db_name>’, ‘single user’, ‘true' DBCC CHECKDB(‘<db_name>’) Go begin tran update sysdatabases set status = &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rior value> where name = ‘<db_name>' — verify one row is updated before committing commit tran Go 7. Turn off the updates to system tables by using: sp_configure ‘allow updates’, 0 reconfigure with override Go <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Hi,
have you replace / overwrite the .mdf file ! and still want let you access the db !
have you tried using STELLAR PHONIX to recover lost/deleted/formatted data ! Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Hi Vietcave,
have you tried what suggested by Satya ?! i am very interested in result! are you able to recover your database ! Regards
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

]]>