SQL Server Performance Forum – Threads Archive

Stupid "admin" question of the week

No, the question isn’t stupid but the "admin" sure is…. BUT, if you can help, please do! The problem is how to recover a .mdf file into an operational db without an .ldf file and no useful backups. See, I told you – STUPID!! Now, I’ve seen plenty of suggestions for SQL 2K, but not 2005. Single file attach – no go. Detach wasn’t proper. DBCC REBUILD_LOG, where most SQL 2K solutions end up – can’t get there on 2005. At least not in any method I’ve tried. 3rd party recovery tools? Anything….? (This isn’t a big db, ~ 8GB, but it is rather important AND urgent). Many thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Gary

Check BOL CREATE DATABASE with FOR ATTACH_REBUILD_LOG option which equivalent to DBCC REBUILDLOG… FOR ATTACH_REBUILD_LOG
Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. If one or more transaction log files are missing, the log file is rebuilt. There must be a <filespec> entry specifying the primary file. Note:
If the log files are available, the Database Engine will use those files instead of rebuilding the log files. FOR ATTACH_REBUILD_LOG requires the following: A clean shutdown of the database.
All data files (MDF and NDF) must be available.
Important:
This operation breaks the log backup chain. We recommend that a full database backup be performed after the operation is completed. For more information, see BACKUP (Transact-SQL). Typically, FOR ATTACH_REBUILD_LOG is used when you copy a read/write database with a large log to another server where the copy will be used mostly, or only, for read operations, and will therefore require less log space than the original database. FOR ATTACH_REBUILD_LOG cannot be specified on a database snapshot. MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thank you!! But,

quote:FOR ATTACH_REBUILD_LOG requires the following: A clean shutdown of the database.

I don’t think I have that, though I’m trying to think why at the moment. Seems in some of my efforts, I got an error message that complained it wasn’t shutdown clean. I’ll try an get more info. If I don’t have a clean shutdown, am I totally out of luck? Thanks again!
Gary

Hi Gary, Assuming you have valid mdf file available,given are some steps you may try
Create another DB with same database name, logical and physical file name on the same path;
detach it cleanly,
then try to re-attach database with newly created log file with old data file
Regards
Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

Yes, here was the response:

quote:File activation failure. The physical file name "L:db1_log" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘asd_db1’. CREATE DATABASE is aborted.

I believe we did a detach database in SSMS after the .ldf file was toast.

Hi Gary, have you tried the option I have given Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

Hi ghemant, Just crossing posts…thank you for your kind suggestion. I just tried it and unfortunately get:

quote:TITLE: Microsoft SQL Server Management Studio
—————————— Attach database failed for Server ‘DB1’. (Microsoft.SqlServer.Smo) For help, click:http://go.microsoft.com/fwlink?Prod…ext&EvtID=Attach database Server&LinkId=20476 ——————————
ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error: 5173) For help, click:http://go.microsoft.com/fwlink?Prod…42&EvtSrc=MSSQLServer&EvtID=5173&LinkId=20476 ——————————
BUTTONS: OK
——————————

Thank you very much for your present and continued suggestions…. I’m Eastern US, and am off to try an get a few hours sleep. Check back in 6 hours, and thank you so much for your time all!
Gary

You can can use ALTER DATABASE to set emergency mode and refer tohttp://sqlserver-qa.net/blogs/tools…transaction-log-file-on-test-server-only.aspx process. If this is an urgent requirement I would suggest to go to MS PSS to recover, as they will have exclusive tools to get it back. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thank you all! I did get a solution that required a little trick to get to the point where I could use ALTER DATABASE for emergency mode and so forth. Came from a member at another forum, but will share if rules permit.

The same what I have suggested above [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]/ Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> @<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>