| Author |
Topic |
|
kifeda
Posting Yak Master
136 Posts |
 Posted - 2006-11-16 : 12:14:12
|
| 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
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-16 : 12:30:33
|
| "Taking" the MDF is not backing up the database. Polish up your resume.Jay White |
 |
|
|
kifeda
Posting Yak Master
136 Posts |
Posted - 2006-11-16 : 12:39:13
|
| 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? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-16 : 12:43:37
|
| 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 |
|
|
|
kifeda
Posting Yak Master
136 Posts |
Posted - 2006-11-16 : 12:52:11
|
| 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? |
|
|
|
Kristen
Test
22859 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-16 : 13:50:35
|
| Just restore the database from one of your database backups.That always works for me.CODO ERGO SUM |
|
|
|
kifeda
Posting Yak Master
136 Posts |
Posted - 2006-11-16 : 14:42:59
|
| 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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-16 : 15:33:38
|
| "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! |
|
|
|
dekj
Starting Member
4 Posts |
Posted - 2011-02-01 : 18:50:24
|
| 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, enternet 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 hours3) Restart the SQL Server and SQL Server Agent. . At a command prompt, enternet 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, enternet 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 names9) Restart the SQL Server and SQL Server Agent. . At a command prompt, enternet 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 modealter database POSProd set emergency12) Set the database to single user mode. Without this, the checkdb command won’t run.ALTER DATABASE [POSProd] SET SINGLE_USER WITH NO_WAIT13) Run dbcc checkdbdbcc 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 modeALTER DATABASE [POSProd] SET MULTI_USER WITH NO_WAIT15) Set the database online.alter database POSProd set online16) 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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-02-01 : 19:58:44
|
| "...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 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-02 : 09:46:08
|
| 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?" |
|
|
|
drdesouza
Starting Member
6 Posts |
Posted - 2013-03-29 : 07:27:00
|
| HiIf 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 |
|
|
|
|
Trying to restore with no Log file!!!!