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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 missing ldf file can't attach or

Author  Topic 

sleaklight
Starting Member

11 Posts

Posted - 2006-12-20 : 00:11:09
being the newbie to sql databases, I would stop sql server and simply copy the mdf file and kept tit as backups. Never thought I needed the logfile. Now that I lost a website due to an HD crash, or hacker, I am wanting to put those mdf file back and have it working. Turns out that is not so. I checked through the forums and found a few topics, found this one in particular:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37161&whichpage=1
I did everything listed in there without luck. I even tried the link to programmerspace.com without luck.
I did the following in the query analyzer:

EXEC sp_attach_single_file_db @dbname = 'weeweeslap',
@physname = 'c:\sql_backups\weeweeslap_Data.mdf'

then tried:

sp_attach_db @dbname = N'weeweeslap',
@filename1 = N'C:\sql_backups\weeweeslap_Data.MDF'

then tried:
SP_ATTACH_SINGLE_FILE_DB'weeweeslap','C:\sql_backups\weeweeslap_Data.MDF'

all without luck and saying the same error:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'weeweeslap'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL

Server\MSSQL\Data\weeweeslap_log.LDF' may be incorrect.



Please guys I'd really appreciate it if you can help me. I have dedicated 5 years of my life to this website and I simply can't let it go because of this. PLease help me find a solution to get my db up and running again. I appreciate any and all info that you can give to fix this problem. Thank you very much.
Oscar

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-20 : 00:31:10
did you reattach on the same server or different server?
try to attach the mdf using enterprise manager so you can see if the directories are correct.

HTH

--------------------
keeping it simple...
Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 2006-12-20 : 00:50:09
different server, old one the hard drive crashed.
I tried attaching using EM, qudruple checked the directories as well. Thanks
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-20 : 10:34:33
Interesting that it is trying to create the LDF file in:

'C:\Program Files\Microsoft SQL Server\MSSQL\Data\weeweeslap_log.LDF'

make sure that that folder exists (even if you have no intention of hosting the LDF file there!)

BoL says:
"When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.

Use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.
"

Double check that all that is OK

P.S. Please use SQL BACKUP, rather than File Copy, in future!

Kristen
Go to Top of Page

COCOTON
Starting Member

2 Posts

Posted - 2006-12-20 : 12:49:03
I am tried to recover a data base SQL, I have a backup complete endorsement to the 24 of January of the 2006, and have all the daily backup's of transactions. But the following error appears to me when I am going to recover log of the 31 of January of the 2006:
Server: Msg 4326, Level 16, State 1, Line 2
The log in this backup set terminates at LSN 11000000247500001, which is too early to apply to the database. A more recent log backup that includes LSN 11000000405300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.

Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 2006-12-20 : 13:00:12
quote:
Originally posted by Kristen

Interesting that it is trying to create the LDF file in:

'C:\Program Files\Microsoft SQL Server\MSSQL\Data\weeweeslap_log.LDF'

make sure that that folder exists (even if you have no intention of hosting the LDF file there!)

BoL says:
"When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.

Use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.
"

Double check that all that is OK

P.S. Please use SQL BACKUP, rather than File Copy, in future!

Kristen


Yes, I double checked everything and I am still without luck
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-21 : 02:02:33
quote:

I would stop sql server and simply copy the mdf file and kept tit as backups



i missed this part, you should have detached the database first before you did that step, then reattached...

have you recovered from the crash? did you have tape backups of the server? if yes, you can restore everything on a new machine and replace the mdf file (whichever is latest) and hope that it works

do a simple backup in the future

--------------------
keeping it simple...
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-21 : 02:20:01
restore the full backup first then the log backups...
if it's a series of log backups, you need to apply them one by one after the last full backup has been restored

quote:
Originally posted by COCOTON

I am tried to recover a data base SQL, I have a backup complete endorsement to the 24 of January of the 2006, and have all the daily backup's of transactions. But the following error appears to me when I am going to recover log of the 31 of January of the 2006:
Server: Msg 4326, Level 16, State 1, Line 2
The log in this backup set terminates at LSN 11000000247500001, which is too early to apply to the database. A more recent log backup that includes LSN 11000000405300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.





--------------------
keeping it simple...
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-21 : 02:39:25
quote:
Originally posted by COCOTON

I am tried to recover a data base SQL, I have a backup complete endorsement to the 24 of January of the 2006, and have all the daily backup's of transactions. But the following error appears to me when I am going to recover log of the 31 of January of the 2006:
Server: Msg 4326, Level 16, State 1, Line 2
The log in this backup set terminates at LSN 11000000247500001, which is too early to apply to the database. A more recent log backup that includes LSN 11000000405300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally





Don't hijack this thread. Create a new thread and post your question there. If you don't you probably won't get the response you want.



-ec
Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 2006-12-21 : 02:43:59
I posted this problem at snitz andthey helped me out and we're back in business, thankfully. I totally appreciate you guys helping me out, let me copy and paste what they posted for me to get it working.
quote:

HuwR
Semi-Retired Admin



United Kingdom
14748 Posts
Posted - 20 December 2006 : 08:35:56
--------------------------------------------------------------------------------

like we said, you do really really need the log file, however you could try this

Undocumented DBCC Command REBUILD_LOG

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')


/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE MyDatabase SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO



Maybe you guys can have an article or add this to your articles on restoring without ldf. Thank you guys and gals again!
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-21 : 02:49:28
quote:
Originally posted by sleaklight

quote:
Originally posted by Kristen

Interesting that it is trying to create the LDF file in:

'C:\Program Files\Microsoft SQL Server\MSSQL\Data\weeweeslap_log.LDF'

make sure that that folder exists (even if you have no intention of hosting the LDF file there!)

BoL says:
"When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.

Use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.
"

Double check that all that is OK

P.S. Please use SQL BACKUP, rather than File Copy, in future!

Kristen


Yes, I double checked everything and I am still without luck




you are certain that you have a 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\' directory? I'm almost afraid to ask, but what data do you store in a database called weeweeslap?

Anyway, give these steps a shot (from http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599). Make sure you use a copy of the .MDF file you have, not the original.

shutdown sql

move the current database file or rename it

restart sql server

create a new database of the same name and log file and location as the old database and log file

get rid of the old database.
you may be able to right click delete it in this situation or used sp_removedb

create a new database of the right size and shape with correct log and data file locations

stop sql

rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf

move back in the old database .mdf file or rename it back again

restart sql server

it should come up suspect
--------------------------------
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go

2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:

update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go

3. Go into the data directory (MSSQL7\DATA) and rename the log file associated
the DB in question (XXXX.ldf) to some
temporary name, such as XXXX.TMP.

4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency mode
by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'

7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log ('<DB_Name>','<log_filename>') <--- You will need
the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
restart SQL server. Verify the status of the
database by running DBCC NEWALLOC and DBCC CHECKDB on the database.




-ec
Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 2006-12-21 : 04:08:54
thanks but the problem has been solved with the steps listed in my previous post
Also the site is http://www.weeweeslap.com if you want to check it out, no porn! Thanks again!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-21 : 07:18:50
Time to put a proper backup in place then ...

Kristen
Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 2006-12-21 : 12:42:45
lol, yes
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 13:13:03
quote:
Originally posted by jen

quote:

I would stop sql server and simply copy the mdf file and kept tit as backups



i missed this part, you should have detached the database first before you did that step, then reattached...




This part isn't true. Stopping the SQL Server service does the same thing as detaching the database. Both release the database files. So if you are only going to take cold backups (which copying of files is), then you can either detach the database first or stop the service before copying the file.

Neither method guarantees that the file is attachable though. The only way to know for sure that the file was copied correctly is to perform the attach each day just like you would restore your backups as a test.

Tara Kizer
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-22 : 11:12:00
By the by, BACKUP will most probably generate a smaller file than the original MDF (as it only backs up the USED portion of the file), and it can be used whilst the database is running, hence preferred. Doesn't guarantee that the backup file is restorable though ... to be sure of that you need to make a trial restore (to a different database, or server) AND perform a DBCC CHECKDB on the restored database.

For mission critical systems you should do that after every backup.

Kristen
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-04 : 10:39:31
what does it mean?
"create a new database of the right size and shape with correct log and data file locations"

Because u r saying to create a new database already on the 4th line?
so should i create it again with a different name?

=============================
http://www.sqlserverstudy.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 10:51:40
So you were Sleaklight?
How many aliases do you have here at SQLTeam?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2007-09-04 : 12:59:08
no.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 13:01:04
quote:
Originally posted by gongxia649

no.
Now you have proven to be Funketekun too.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-04 : 13:02:11
Well there's two: funketekun and gongxia649. I think they just hijacked this thread since the IP address of sleaklight is different from these other two.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
    Next Page

- Advertisement -