Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-07-16
35
Medium Priority
?
4,835 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:hillandale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 12
  • 3
35 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 19502599
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
 
LVL 12

Accepted Solution

by:
patrikt earned 2000 total points
ID: 19502606
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
 
LVL 12

Expert Comment

by:patrikt
ID: 19502621
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:hillandale
ID: 19502628
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
 

Author Comment

by:hillandale
ID: 19502641
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
 
LVL 14

Expert Comment

by:twoboats
ID: 19502665
Hey patrikt - great minds ;)

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

Fingers crossed ....
0
 
LVL 12

Expert Comment

by:patrikt
ID: 19502666
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
 

Author Comment

by:hillandale
ID: 19502685
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
 
LVL 12

Expert Comment

by:patrikt
ID: 19502737
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
 

Author Comment

by:hillandale
ID: 19502738
Layers of stupid.... still working...
0
 

Author Comment

by:hillandale
ID: 19502781
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
 

Author Comment

by:hillandale
ID: 19502787
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
 

Author Comment

by:hillandale
ID: 19502789
where the little boxes are single quotes
0
 
LVL 12

Expert Comment

by:patrikt
ID: 19502796
What is the error there?
0
 

Author Comment

by:hillandale
ID: 19502797
Got it now....proceeding
0
 

Author Comment

by:hillandale
ID: 19502817
Executing the checkb now....
0
 
LVL 12

Expert Comment

by:patrikt
ID: 19502828
Save the output of DBCC for later check. It can help you find losses of data.
0
 

Author Comment

by:hillandale
ID: 19502839
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
 

Author Comment

by:hillandale
ID: 19502852
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
 
LVL 12

Expert Comment

by:patrikt
ID: 19502869
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
 

Author Comment

by:hillandale
ID: 19502880
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
 
LVL 12

Expert Comment

by:patrikt
ID: 19502885
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
 

Author Comment

by:hillandale
ID: 19502908
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
 
LVL 12

Expert Comment

by:patrikt
ID: 19502923
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
 

Author Comment

by:hillandale
ID: 19502927
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
 
LVL 14

Expert Comment

by:twoboats
ID: 19502931
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
 

Author Comment

by:hillandale
ID: 19502937
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
 

Author Comment

by:hillandale
ID: 19502950
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
 
LVL 12

Expert Comment

by:patrikt
ID: 19502952
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
 
LVL 12

Expert Comment

by:patrikt
ID: 19502953
0
 

Author Comment

by:hillandale
ID: 19502954
No no, completely a mistake on my part. You deserve 2X the points. Really. I'll fix if I can.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 19502966
:) :) 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
 

Author Comment

by:hillandale
ID: 19502999
Thank you patrikt!
0
 

Author Comment

by:hillandale
ID: 19507724
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question