Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpiscit1
Posting Yak Master
130 Posts |
 Posted - 2002-12-31 : 11:38:46
|
| I walked into a messy one that I hope can be corrected. A log file was deleted (LDF). The Database file remains (MDF). There are log back ups (TRN) for the last two days. No database backup. Anyway to recover the logs without losing what data was in the database from the TRN files? I apoligize for my lack of knowledge in this area. I hope someone can help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-31 : 12:26:36
|
| You should be able to just create a new log file. Have you tried starting up SQL Server yet? If so, is the database available? If it is, take an immediate backup of it. If SQL Server isn't started up yet, try starting it. If you get an error, then post the error back in this thread. |
 |
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2002-12-31 : 12:40:09
|
| There is always more to the story so here it is.My original intent was to reduce teh size of the log file as it had grown uncontrollably. I have fixed the problem with that. I detached the database and deleted the log file. I did this after reading an article here that said you could do that to quickly reduce a log file size. In essence it will create another one. In any case, it says to re-attach the database using sp_attach_db @dbname = N'StockPrep', @filename1 = N'd:\SQL\Databases\StockPrp_Data.MDF'Somthing like this.When I do I get an error stating it cannot create the database due to the log file reference being incorrect.The database was small and can easily be recreated. Its the data I wanted. As it stands now i have already created a new one. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-31 : 12:43:26
|
| You need to run sp_attach_single_file_db instead of sp_attach_db.Here is what you will need to run:sp_attach_single_file_db @dbname = 'StockPrep', @physname = 'd:\SQL\Databases\StockPrp_Data.mdf'SQL Server will create the transaction log file for you. |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2002-12-31 : 13:01:07
|
| When I do run that, this is what I get:Server: Msg 1813, Level 16, State 2, Line 1Could not open new database 'StockPrep'. CREATE DATABASE is aborted.Device activation error. The physical file name 'D:\SQL\Logs\StockPrep_Log.LDF' may be incorrect. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-31 : 13:23:21
|
| Check to see if D:\SQL\Logs\StockPrep_Log.LDF exists. If it does, delete it. Then run the command again. If you are unable to delete it, then stop and start the SQL Server so that it releases it. |
|
|
|
victorious
Starting Member
3 Posts |
Posted - 2005-02-26 : 13:31:23
|
| Hi! I have exactly the same problemI have four MDFsH:\Data1.mdfH:\Data2.mdfH:\Data3.mdfQ:\Data4.mdfTRNs the last three days and a missing LDF.Should I apply the same solution? Please HELP ME!!! |
|
|
|
|
|
|
|
|
Recovering Database with only a log back up (TRN)