SQL 2005 (not 2000!) recovery from lost transaction log?

Gary

Seen it done this way for 2000 - dunno if it will work for 2005.... but if you try, and it does, let us know.
(apologies if you've already tried this)

1. Create a new db, same name, same MDF and LDF files

2. Stop sql server.

3 Copy your MDF over the one just created.

4 Delete the LDF just created

5. Start SQL Server

6. DB should be marked suspect

7. Set DB to Emergency mode in sysdatabases.

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

8 Start SQL

7. Now build the log

DBCC REBUILD_LOG(databasename,'log.ldf')

9. Execute sp_resetstatus databasename

10. stop and start SQL

For twoboats: Second time we meet with same answer  :)

The SQL 2005 will not allow sysdatabases (as any other sys...) update. But fortunately there are commands for this (see my post). It is more secure and clear now on 2005.

twoboats - Thank you, and I have tried that. The "Update sysdatabases set status = 32768 where name = "BadDbName" " part gets an "adhoc queries not allowed on system databases" or some such. An MS blog entry indicated they purposely locked that avenue down in 2005....Many thanks,m though!

patrikt - thanks....pending here. I'm not clear on your first steps. I'm assuming (4) is put my old (bad) .mdf file in place of a new clean .mdf file just created...

Hey patrikt - great minds ;)

Understood Gary - looks like patrikt has a way round the lock down. Useful to know.

Fingers crossed ....

Yes, as you write. When SQL is stopped put <bad>.mdf on place of <new>.mdf
It has to have same name and same size, if not create better "fake db".

Thanks for the clarification. I'm working on it. Slowing me down that the original file(s) didn't appear to have extensions (.ldf/.mdf), and I can't see that SMSS supports a name without extensions. So, got to rename or T-SQL it, and I'm not too quick :)

I'm not sure if it will work if you do rename. Full file name is in file header written so it can cause problems.
It is verry uncommon to have files not marked as mdf/ldf. Are you sure it is datafile?

Layers of stupid.... still working...

Yes, I don't know how they weren't, but quite sure the data and log files didn't have any extension. Odd, but wouldn't be the first thing.... assembling a create statement now, just have some little syntax issue. (and yes, the rename/alter didn't work worth a flip).

Doesn't like this:
CREATE DATABASE asd_db1 on (name = db1_data, filename = D:\db1_data, size = 6870592KB, filegrowth = 10%)  LOG ON (name = db1_log, filename = L:\db1_log, filegrowth = 10%)

where the little boxes are single quotes

What is the error there?

Got it now....proceeding

Executing the checkb now....

Save the output of DBCC for later check. It can help you find losses of data.

It worked very hard, then gave me this - The first line doesn't look very nice, but the table names and rowcounts look about right.....

Msg 5173, Level 16, State 1, Line 1
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.
Log file 'L:\db1_log' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
The Service Broker in database "asd_db1" will be disabled because the Service Broker GUID in the database (EB8926D0-ECF3-487B-B26A-0C6E805BB93A) does not match the one in sys.databases (CD26095E-FEFC-4F34-A6DD-56A39B773C84).
Warning: The log for database 'asd_db1' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
DBCC results for 'asd_db1'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1202 rows in 13 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 162 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 194 rows in 4 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 1202 rows in 13 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 162 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 194 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 174 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 359 rows in 8 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1373 rows in 28 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 11 rows in 1 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 31 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 356 rows in 6 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 498 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 549 rows in 143 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 176 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 506 rows in 4 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'tblFailedLogin'.
There are 12 rows in 2 pages for object "tblFailedLogin".
DBCC results for 'sys.queue_messages_16719112'.
There are 0 rows in 0 pages for object "sys.queue_messages_16719112".
DBCC results for 'tblEvent'.
There are 4517598 rows in 70241 pages for object "tblEvent".
DBCC results for 'sntLogCallError'.
There are 554 rows in 6 pages for object "sntLogCallError".
DBCC results for 'sys.queue_messages_48719226'.
There are 0 rows in 0 pages for object "sys.queue_messages_48719226".
DBCC results for 'sntLogCall'.
There are 111123 rows in 581 pages for object "sntLogCall".
DBCC results for 'arc_tblLocation'.
There are 16 rows in 1 pages for object "arc_tblLocation".
DBCC results for 'sntLogBusy'.
There are 211299 rows in 605 pages for object "sntLogBusy".
DBCC results for 'sys.queue_messages_80719340'.
There are 0 rows in 0 pages for object "sys.queue_messages_80719340".
DBCC results for 'tblEventArchive'.
There are 2518421 rows in 39483 pages for object "tblEventArchive".
DBCC results for 'sntLogAction'.
There are 411216 rows in 2973 pages for object "sntLogAction".
DBCC results for 'tblMessage'.
There are 0 rows in 0 pages for object "tblMessage".
DBCC results for 'vbvFaxQueue'.
There are 13 rows in 2 pages for object "vbvFaxQueue".
DBCC results for 'wvmSupport-old'.
There are 19 rows in 1 pages for object "wvmSupport-old".
DBCC results for 'tblVendor'.
There are 0 rows in 0 pages for object "tblVendor".
DBCC results for 'tblLocDefaults'.
There are 0 rows in 0 pages for object "tblLocDefaults".
DBCC results for 'voViewObit'.
There are 185 rows in 2 pages for object "voViewObit".
DBCC results for 'tblObit'.
There are 125143 rows in 10523 pages for object "tblObit".
DBCC results for 'voContact'.
There are 135 rows in 19 pages for object "voContact".
DBCC results for 'cdr_1'.
There are 521778 rows in 74557 pages for object "cdr_1".
DBCC results for 'wvmGroups'.
There are 11 rows in 1 pages for object "wvmGroups".
DBCC results for 'tblThumb'.
There are 0 rows in 0 pages for object "tblThumb".
DBCC results for 'wvmSupport2'.
There are 14 rows in 1 pages for object "wvmSupport2".
DBCC results for 'arc_tblObit'.
There are 1644 rows in 80 pages for object "arc_tblObit".
DBCC results for 'acdDID'.
There are 52 rows in 1 pages for object "acdDID".
DBCC results for 'arc_tblAccount'.
There are 19 rows in 1 pages for object "arc_tblAccount".
DBCC results for 'tblImage'.
There are 17 rows in 1 pages for object "tblImage".
DBCC results for 'acdAccount'.
There are 30 rows in 1 pages for object "acdAccount".
DBCC results for 'acdAgent'.
There are 3 rows in 1 pages for object "acdAgent".
DBCC results for 'sysdiagrams'.
There are 2 rows in 1 pages for object "sysdiagrams".
DBCC results for 'SurveyQuestion'.
There are 27 rows in 1 pages for object "SurveyQuestion".
DBCC results for 'acdStation'.
There are 14 rows in 1 pages for object "acdStation".
DBCC results for 'Results'.
There are 32 rows in 1 pages for object "Results".
DBCC results for 'SurveyQuestionAnswer'.
There are 164 rows in 3 pages for object "SurveyQuestionAnswer".
DBCC results for 'dtproperties'.
There are 7 rows in 1 pages for object "dtproperties".
DBCC results for 'Survey'.
There are 7 rows in 1 pages for object "Survey".
DBCC results for 'AccountAnswer'.
There are 5915 rows in 123 pages for object "AccountAnswer".
DBCC results for 'tblAccount'.
There are 4567 rows in 137 pages for object "tblAccount".
DBCC results for 'tblSession'.
There are 133 rows in 5 pages for object "tblSession".
DBCC results for 'arc_AccountAnswer'.
There are 0 rows in 0 pages for object "arc_AccountAnswer".
DBCC results for 'arc_Survey'.
There are 0 rows in 0 pages for object "arc_Survey".
DBCC results for 'arc_SurveyQuestion'.
There are 0 rows in 0 pages for object "arc_SurveyQuestion".
DBCC results for 'tblBillingLog'.
There are 5430550 rows in 75778 pages for object "tblBillingLog".
DBCC results for 'arc_SurveyQuestionAnswer'.
There are 0 rows in 0 pages for object "arc_SurveyQuestionAnswer".
DBCC results for 'tblLocation'.
There are 4836 rows in 115 pages for object "tblLocation".
DBCC results for 'arc_tblMemory'.
There are 0 rows in 0 pages for object "arc_tblMemory".
DBCC results for 'tblAccountMaster'.
There are 3 rows in 1 pages for object "tblAccountMaster".
DBCC results for 'tblSMCEventLog'.
There are 8240987 rows in 120918 pages for object "tblSMCEventLog".
DBCC results for 'normanEvents'.
There are 1450314 rows in 22821 pages for object "normanEvents".
DBCC results for 'tblBillingTypes'.
There are 24 rows in 1 pages for object "tblBillingTypes".
DBCC results for 'cdr'.
There are 48102 rows in 6905 pages for object "cdr".
DBCC results for 'BillingLogID'.
There are 0 rows in 0 pages for object "BillingLogID".
DBCC results for 'tblMemory'.
There are 0 rows in 0 pages for object "tblMemory".
DBCC results for 'arc_tblMemoryBackup'.
There are 0 rows in 0 pages for object "arc_tblMemoryBackup".
DBCC results for 'tblMemoryBackup'.
There are 0 rows in 0 pages for object "tblMemoryBackup".
DBCC results for 'arc_tblMessage'.
There are 0 rows in 0 pages for object "arc_tblMessage".
DBCC results for 'voPage'.
There are 90 rows in 12 pages for object "voPage".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'asd_db1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So, is that what you expect to get from this? And, what's next.... hand-check the data? Or do I need to mess with any other validation, backup, configuration operations first?

OK, You have done it! :)

But now the bad news...
The database will not contain consistent data because transactional consistency was lost with ldf file.
It means that you couln't expect dataloss to comply with timeline (means "lost till 12:55" is not true). Some transactions are ok and some ware lost regardless of time.

Check your data wery hard before starting application over it. Or do backup copy in current state so you can go back later.

OK, I understand that rows lost will not be time dependent. Would you expect the schema to be intact? Or hopefully/maybe? And, I assume any indexes should be rebuilt? This isn't a very complex schema, with few relations, so I'm hoping things are decent. Last backup was 3 weeks ago (for other stupid reasons), so this is likely an improvement!

I suggest:

1) Let DB stay in SINGLE_USER to prevent others to mess. Hand-check data.
2) Setup backup.
3) Start MULTI_USER and allow application to start. Check data through application(s).

And may be first of all found the source of this problem and make decisions how to not allow it in future. :)

So, as I'm obviously way over my head with this (like: oh no! the transaction log volume is full...users are getting kicked off....). Do a truncate, a shrinkfile, nope. Must've done it wrong....well, how about.... delete the transaction log file! Yes, that'll do it......AAARRRRRRRRGGGGGGGGGHHHHH.

Don't ask what short-circuit in the brain allowed that, but I REALLY appreciate you walking me patiently through this. Believe me, I'm new to db, but not new at all to admin and cut my teeth a number of years on end-user support ("yes, that's right. Then press the "Enter" key). It's wierd to be the on the receiving end. I love this tool (SQL), but obviously have a LONG ways to go.

MANY MANY MANY THANKS!

You will need:
DBCC CHECKDB
DBCC CHECKTABLE
ALTER INDEX ... REBUILD

Schema will be 99% ok because you have not corrupted MDF file.
Indexes should be nearly OK but lost data rows should be wrong to it. Better reindex all.
Statistisc will be same as indexes. Better to run UPDATE STATISTICS ... on all.

In fact, the backup came from a system I had setup to take log shipping, but it didn't look right as it was always in "restore" mode. So, I pulled the plug to move it to a beefier machine, etc. etc. Come to find out (tonight) that it was apparently working... That's where the 3 week old db copy comes from. Yes, lots to learn....

Thanks again. You've provided a ray of hope :) And my wife's prayers were answered.
Gary

Nice one.

Just to re-iterate. Get yourself to a state where you're happy with the data, and take a full backup.

Then, personally, I'd do a backup, and restore it back to a clean database - so that you know for sure that there aren't any gotchas waiting for you, having pulled your data back from just the mdf

Yes, I'll follow up wiith the utilities. Not sure what to look for, but a great learning opportunity. (And a heads-up to management that admin really IS important - even if it's not as sexy as development).

Well, crud. What next? Newbie mistake and hit the wrong 'accept' button for you patrikt! After all of that. I would imagine the moderator can correct. I'll follow up on it - sorry. And, thanks twoboats for listening in and following up.
Gary

Sorry guy but it was not a wery nice to me :(

I have nothing against twoboot but you should at least split points between us.

https://www.experts-exchange.com/help.jsp#hs5

No no, completely a mistake on my part. You deserve 2X the points. Really. I'll fix if I can.

:) :) I know you are new to EE.
Read the help and you will find a way how to correct it if you want.
There is Community Support zone to help you.

Thank you patrikt!

Just FYI, the db is back and online as of a few hours ago. Have not discovered any data loss, though I'm sure there's something, though it would be minor. A great recovery and a great fix. Excellent advice, patrikt!

** And you've got the points to show for it now, too :)