SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Trying to restore with no Log file!!!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kifeda
Posting Yak Master

136 Posts

Posted - 11/16/2006 :  12:14:12  Show Profile  Reply with Quote
I reformatted the server and only took the MDF file and not the log file. How can I restore the database without the log file???? Any help will be greatly appreciated! I'm dying here....

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/16/2006 :  12:30:33  Show Profile  Reply with Quote
"Taking" the MDF is not backing up the database. Polish up your resume.

Jay White
Go to Top of Page

kifeda
Posting Yak Master

136 Posts

Posted - 11/16/2006 :  12:39:13  Show Profile  Reply with Quote
what do you mean? I only have the MDF file. I do not have the LDF Log file. Is there a way to restore the database file with only the MDF file?
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/16/2006 :  12:43:37  Show Profile  Reply with Quote
Did you shut down the SQL Server process before you copied off the .MDF?

If so, you may be able to us sp_attach_single_file_db ...
If not, there is no way to restore the database and you are screwed. Sorry.

Jay White
Go to Top of Page

kifeda
Posting Yak Master

136 Posts

Posted - 11/16/2006 :  12:52:11  Show Profile  Reply with Quote
I don't think so. The server crashed and I just copied the MDF file from the data directory but I did not copy the LOF file. There is no extraction tool that I can use?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/16/2006 :  13:26:58  Show Profile  Reply with Quote
Try the single file attach - for more details see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=single%20file%20attach,sp_attach_single_file_db

If it seems to attach OK it would be a good idea to check the database - see DBCC CHECKDB in BoL

If that works out OK put a backup procedure in place before you come close to coming a cropper again!

If that doesn't work you are hosed, as Page47 said. I very much doubt you will have any joy whatsoever with an "extraction tool" approach, unless you have deep pockets.

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/16/2006 :  13:50:35  Show Profile  Reply with Quote
Just restore the database from one of your database backups.

That always works for me.





CODO ERGO SUM
Go to Top of Page

kifeda
Posting Yak Master

136 Posts

Posted - 11/16/2006 :  14:42:59  Show Profile  Reply with Quote
i tried the single file approach and got this error:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL\data\sbc-studioapp_Log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/16/2006 :  14:59:46  Show Profile  Reply with Quote
Create an empty databse with hthe sae name and location of the files as the old one

Dump it, then attach using that log file

before you do that, go around and back up any other databases that you hae



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/16/2006 :  15:33:38  Show Profile  Reply with Quote
"before you do that, go around and back up any other databases that you hae"

I'll buy you a pint if the OP takes your advice!

Go to Top of Page

dekj
Starting Member

USA
4 Posts

Posted - 02/01/2011 :  18:50:24  Show Profile  Reply with Quote
I found myself in the position of having an MDF file (and an NDF file, a expansion file that placed several tables on another drive), with no LDF log file and no backup. Things happen in life, and this happened to me. As it turns out, SQL Server will not act on any database without a log file. Period. Upon googling this situation, I found that there were ways of recreating the log file in SQL Server 2003 and 2005, but those mechanism were taken out of SQL Server 2008, the version I was running on. I also found the “SQL Server experts” that answered the calls of help for this situation in internet forums were of no help. They mainly seemed to respond by describing the hapless users in this situation as idiots residing in a hopeless situation.

I found a way to recreate the log file. I tried many things, and eventually found myself with the database, which I will call POSProd throughout this example, offline and with no way to bring it online. Here is the step-by-step procedure I eventually followed.

1) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

2) Rename out the old MDF (and NDF) files. Copy them as well if you have space, so if you eventually corrupt the original files, you will still have something to work with. My files were over 2 TB long, and this took 6 hours

3) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

4) In SQL Server Management Studio, right-click the POSProd database, and delete it.

5) Right-click Databases, and create a new POSProd database, with the same files as the old one. For me, this included the MDF file, the LDF file, and the NDF file.

6) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

7) Delete the new MDF (and NDF) files, but leave the LDF log file.

8) Rename the old MDF (and NDF) files back to their original names

9) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

10) At this point, you will have a POSProd database pointing to the correct database files, but also to an LDF log file that is still not attached to them. It seems that they are all related with GUIDs or something, and the LDF GUID is still incorrect, so you will still not be able to act on the database. DON’T DO ANYTHING ELSE WITH THE DATABASE EXCEPT WHAT I HAVE DONE BELOW. I don’t know what effect that will have on the database.

11) Open a new query in SQL Server. Alter the POSProd database to emergency mode

alter database POSProd set emergency

12) Set the database to single user mode. Without this, the checkdb command won’t run.

ALTER DATABASE [POSProd] SET SINGLE_USER WITH NO_WAIT

13) Run dbcc checkdb

dbcc checkdb ('POSProd',repair_allow_data_loss)

Once again, because the database was about 2 TB, this ran for about 10 hours. Its listing showed many errors, but also noted that the LDF file was recreated for the original POSProd files. This looked very interesting.

14) Alter the database back to multi user mode

ALTER DATABASE [POSProd] SET MULTI_USER WITH NO_WAIT

15) Set the database online.

alter database POSProd set online

16) Now you still do not have a good database, because SQL Server still knows about the old database. So detach the database by right-clicking the POSProd database, and selecting Tasks / Detach.

17) Attach the database back by right-clicking Databases and selecting Attach. Browse to the MDF file and press OK.

Voila!! The database (in my case) was completely back. All the tables, every row of data.

Good luck if you need this, and remember, DO NOT EVERY DELETE THE LOG FILE. It is as important to SQL Server as the database files, even if (as in my case) there are no transaction boundaries involved in writing to the DB.



dekj
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/01/2011 :  19:58:44  Show Profile  Reply with Quote
"...Things happen in life..."

I'm going to remember that for the next time I find myself with a multi-terabyte database without a backup or log file.


Another good tip to remember on not deleting the log file.





CODO ERGO SUM

Edited by - Michael Valentine Jones on 02/01/2011 20:24:21
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 02/02/2011 :  09:46:08  Show Profile  Visit russell's Homepage  Reply with Quote
And bumped a 4 year old post to share info that is posted here many times...

But, while I wouldn't go so far as to call the people "idiots" there is no excuse for allowing this to happen. Let me put it this way: it never happens to folks who have half a clue what they're doing. I've been on calls where the in-house staff did all kinds of stupid things. Not having good backups is #1 on the list.

One of my favorite meetings with a client went like this:
"We have performance problems ever since migrating to new servers"

"Did you rebuild indexes after migration? Do you have any metrics from the old servers?"

"No, we don't have enough disk space to rebuild indexes"

"You know, running out of disk space can cause the server to crash. How often are you taking backups?"

"We haven't taken any backups since we migrated 3 weeks ago"

"What would happen to your business if you lost all of your data?"
Go to Top of Page

drdesouza
Starting Member

USA
6 Posts

Posted - 03/29/2013 :  07:27:00  Show Profile  Reply with Quote
Hi
If you reformatted the server and you took only .mdf file and want to restore MDF file without .log file. Then doesn’t worry about this situation as I am also trap that kind of circumstances. I tried this advance SQL restore tool and restore my entire SQL database without .log file. You can get addition information here: unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000