why the hell grew it to 61GB? the last backup was about 19MB or so, it only has 4k accounts.
There is an other 500 points for the one helping me to get the data back (1000 points total)
Main Topics
Browse All Topics
Loading Advertisement...
Question
restoring MSSQL DB from .MDF and .LDF (no 3rd -LOG file)
Hi,
this morning I discovered that it wasn't possible to make Inserts anymore because there was a File which grew to > 61GB:
D:\sqldata\MSSQL\LOG\Sales
which took all the HD space.
I then shut down the SQL Server and deleted this file because I thought it only was an error log file.
Now the Enterprise Manager say "SalesLogix (suspect)" - (no items)
D:\sqldata\MSSQL\data\Sale
D:\sqldata\MSSQL\data\Sale
I tried to create a new db in the enterprise manager - the .mdf and .ldf where marked with a green cross, but it also expected to find the -LOG file, which was marked with a red X.
I then tried:
sp_attach_db @dbname = 'SLXTA', @filename1='D:\sqldata\MSS
and it told me:
could not open new database 'SLXTA'. Create Database is aborted.
Device activation error: the physical file name 'D:\sqldata\MSSQL\data\Sal
- although I renamed SalesLogix.MDF to SalesLogix2.MDF it still seams to have hard references to specific paths...
How can I get the DB back up again - with only using the .mdf and .ldf?
What is -LOG anyways? to create a dummy empty one didn't work.
I also tried:
ap_attach_single_file_db but it insisted on the SalesLogix-LOG again:
device activation eroor: the physical file name 'D:\sqldata\MSSQL\data\Sal
Please help or I'll have to look for a new job... :(
Freddy
FOLLOWUP: There is also a backup, 3 weeks old (too old)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Subscribe now for full access to Experts Exchange and get
Instant Access to this Solution
- Plus...
- 30 Day FREE access, no risk, no obligation
- Collaborate with the world's top tech experts
- Unlimited access to our exclusive solution database
- Never be left without tech help again
Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.
- "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
- "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
- "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.
See what Experts Exchange can do for you.
Got a question?
We've got the answer.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
Need individual assistance?
Our experts are ready to help.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Want to learn from the best?
Read articles from industry experts.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Working on a long term project?
Store your work and research.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
What Makes Experts Exchange Unique?
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Trusted by the world's most respected brands.
Faithfully serving IT professionals since 1996.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Free Tech Articles
-
WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
-
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
-
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
-
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
-
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
-
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...
Cloud Class Webinars
- Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
- Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
- IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
- Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
- Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
- How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...
Join the Community
Give a Little. Get a Lot.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Answers
Yes--check out :
http://support.microsoft.c
It tells you how to use a newer log file against an old MDF.
If you have the database in full / bulk logged recovery mode and have the sequence of log file backups from the full backup and have not done anything to upset that sequence (truncate log, change recovery models, ...) then you can restore the backup with no recovery and restore all the sequence of log backups then recover the database.
If you violate the conditions above then you can't.
This may help you figure out how to restore the MDF from my disaster recovery doc from work:
Step 1 – Restore the full backup of the base database through a full database restore.
1. In SQL Enterprise Manager, right-click on the database and select All Tasks | Restore Database
2. In the Restore Database screen, select the “Master” database, and the database radio button
3. Select the backups to restore from the Restore column…select the BAK file to restore the full Master database which is found in C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP and select the latest Master BAK full backup file possible
4. Press OK, and then OK again to restore the database
NOTE: If the database has not been created yet, the restore utility will allow you to specify the database name to restore as and will actually create the database for you and also restore it. Simply put the name of the database that has not been created yet in the field for “Restore as database”
Few questions:
Were you able to view the tables at all in the database.....I mean could you expand that green X to see the tables within that databse?
Here is some background info:
Every SQL Server database has a transaction log file (ldf). A transaction log is a history of modifications to the database, and SQL Server uses it to maintain database integrity. Any changes to the database are written to the transaction log first and then changes are applied to the database. Then, if the database update is successful when person adds a record or updates a record in the databse, the transaction is completed and recorded successfully. If any updates fail, then SQL will use the transaction log to restore the database to its original state (called rolling back the transaction). This allows you to restore the database in case there is an earthquake, power outage, or whatever when you tried to enter a transaction. This is straight from a good book you might want to purchase that I use a lot called "SQL Server 2000 Administrator's Pocket Consultant" written by Microsoft.
I would actually create a test database first in SQL enteriprise manager then try to restore the MDF to it and see if that works. I don't think you will be able to get it more up-to-date then the 3 weeks you talk of unless you have the latest .LDF I think.
I have to go on a trip but let me know on the first question I asked....
Try this book also, very very good and one that I helped use to create my disaster recovery documentation for our servers:
Admin 911 SQL Server 2000
I am new to disaster recovery so I only know the basics here but I think you are out of luck trying to get it back to the latest and greatest data unless you have the MDF and LDF that is dated the day you deleted that file.
Hi Freddy,
You seem to have a couple of things mixed up.
sp_attach attaches exisiting databases - it may create a new log file for you.
You can't create an empty dummy log file by itself.
Creating a database where the filenames are the same as existing files is probably not a good idea.
So try attaching the existing mdf file - I'd suggest that you create a new directory and copy it there. That may allow SQL to create a new log file for you. If the path is there and giving errors then move the existing log file to another directory.
arbert is perfectly correct - while the .ldf is just the log file, there is more to the log file than that.
But try the sp_attach on a copy of the database and let us know how it goes.
Regards
David
Unless anyone else in this forum has anything to add to your latest question, I would just create a test database and call it the same name as your troubled database but with a digit on the end. Then restore your last mdf (from 3 weeks ago) to that test database. Then, open the test database and do some queries just check if most of the data is in there. After that, delete your database that you had problems with and rename the test one to be named with the exact same name as your troubled database and then hopefully if the name is the same, all should maybe check out. Give it a try, this is about all I can provide.
You cannot do "Parts of the .ldf" since you possibly deleted file2 of a 2file ldf. However, you can look at lumigent logexplorer and possibly restore the 3week old file and the "scrounge" the data off part of your ldf (http://www.lumigent.com).
Brett
What I was thinking is that you would only get what info is in the MDF, and that you were out of luck trying to get anymore back beyond 3 weeks trying to use the ldf.....since I don't know much about restoring data from an ldf, that was my only suggestion, using the straight MDF only and whatever info is in that verision of the database.
Hi,
If I can hazzard a slightly unhelpful guess or two
1. I'd suggest that SQL was stopped when the SalesLogix-LOG file was deleted, and since the database wasn't detached cleanly attaching it could be a bit of a problem.
2. The SalesLogix-LOG may have been a log file, but at this distance it is almost as likely to have been a data file ... no?
Suggestion: Have you tried to recover the deleted file? Nortons or somethign similar? As deleted files are recoverable for only so long try this next ... then try to reattach all three files. Then apply dbcc shrinkdb and dbcc shrinkfile to recover extra space in the data and log files. (Shrinking log files is a perinal topic I'm sure the PAQs will have heaps of help there)
Otherwise it looks like the best you are stuck with 3 week old data ... sorry.
Regards
David
Hi, big THANK YOU to everybody who helped me!
I tried to split the points as fair as possible - you guys are just incredible!
M$ finally was able to get the most current data out of it - I don;t know how exactly (yet) they did it.
our sysadmin recorded the session... hope to see/learn about it soon.
I learned _my_ lesson: never just delete a file - even if it seams to be a 'log file' only and even if 20 people are in your office complaining... pressuring you to 'fix' it.
It's not only just 'data' in a database - it's the work of many people.
Big,Big thank you to arbert especially, thanks Brett!
3 Ways to Join
Business Accounts
- Perfect for organizations
- Multiple users
- Save over 36%
- Create a Business Account
Answer for Membership
- Earn free access
- Showcase your knowledge
- No credit card required
- Sign Up to Answer
Loading Advertisement...
by: nigelrivettPosted on 2003-07-18 at 16:36:28ID: 8956065
I suspect that the file you deleted is a second file created to allow extra space for the database.
If it was a data device then you are probably stuck as you won't be able to recover the data.
In fact I think it must be a data device otherwise it wouldn't be giving that error - which is presumably comming from the filegroup record.