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

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 on SQL Server 2005. 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. Access to those system tables not allowed (no "ad-hoc queries"). 3rd party recovery tools? Anything....?

(This isn't a big db, ~ 8GB, but it is rather important AND urgent).

Many thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Gary
hillandaleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

twoboatsCommented:
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
0
patriktCommented:
You will have data loss but it can be done.

1) Detach database and move your mdf to save location.
2) Create new databse of same name, same files, same file location and same file size.
3) Stop SQL server.
4) Swap mdf file of just created DB to your save one.
5) Start SQL. DB will go suspect.
6) ALTER DATABSE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7) DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8) ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABSE <your db> SET ONLINE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patriktCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

hillandaleAuthor Commented:
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!
0
hillandaleAuthor Commented:
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...
0
twoboatsCommented:
Hey patrikt - great minds ;)

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

Fingers crossed ....
0
patriktCommented:
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".
0
hillandaleAuthor Commented:
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 :)
0
patriktCommented:
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?
0
hillandaleAuthor Commented:
Layers of stupid.... still working...
0
hillandaleAuthor Commented:
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).
0
hillandaleAuthor Commented:
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%)
0
hillandaleAuthor Commented:
where the little boxes are single quotes
0
patriktCommented:
What is the error there?
0
hillandaleAuthor Commented:
Got it now....proceeding
0
hillandaleAuthor Commented:
Executing the checkb now....
0
patriktCommented:
Save the output of DBCC for later check. It can help you find losses of data.
0
hillandaleAuthor Commented:
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.
0
hillandaleAuthor Commented:
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?
0
patriktCommented:
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.
0
hillandaleAuthor Commented:
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!
0
patriktCommented:
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. :)
0
hillandaleAuthor Commented:
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!
0
patriktCommented:
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.
0
hillandaleAuthor Commented:
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
0
twoboatsCommented:
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
0
hillandaleAuthor Commented:
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).
0
hillandaleAuthor Commented:
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
0
patriktCommented:
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.
0
hillandaleAuthor Commented:
No no, completely a mistake on my part. You deserve 2X the points. Really. I'll fix if I can.
0
patriktCommented:
:) :) 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.
0
hillandaleAuthor Commented:
Thank you patrikt!
0
hillandaleAuthor Commented:
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 :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.