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)
 missing ldf file can't attach or
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sleaklight
Starting Member

11 Posts

Posted - 12/20/2006 :  00:11:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/20/2006 :  00:31:10  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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 - 12/20/2006 :  00:50:09  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/20/2006 :  10:34:33  Show Profile  Reply with Quote
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

Edited by - Kristen on 12/20/2006 10:35:14
Go to Top of Page

COCOTON
Starting Member

2 Posts

Posted - 12/20/2006 :  12:49:03  Show Profile  Reply with Quote
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 - 12/20/2006 :  13:00:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/21/2006 :  02:02:33  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/21/2006 :  02:20:01  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/21/2006 :  02:39:25  Show Profile  Reply with Quote
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 - 12/21/2006 :  02:43:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/21/2006 :  02:49:28  Show Profile  Reply with Quote
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

Edited by - eyechart on 12/21/2006 02:51:31
Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 12/21/2006 :  04:08:54  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/21/2006 :  07:18:50  Show Profile  Reply with Quote
Time to put a proper backup in place then ...

Kristen
Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 12/21/2006 :  12:42:45  Show Profile  Reply with Quote
lol, yes
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 12/21/2006 :  13:13:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/22/2006 :  11:12:00  Show Profile  Reply with Quote
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

Australia
491 Posts

Posted - 09/04/2007 :  10:39:31  Show Profile  Visit funketekun's Homepage  Reply with Quote
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

Edited by - funketekun on 09/04/2007 10:42:13
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30112 Posts

Posted - 09/04/2007 :  10:51:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Azores
344 Posts

Posted - 09/04/2007 :  12:59:08  Show Profile  Visit gongxia649's Homepage  Reply with Quote
no.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30112 Posts

Posted - 09/04/2007 :  13:01:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by gongxia649

no.
Now you have proven to be Funketekun too.


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 09/04/2007 13:01:51
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 09/04/2007 :  13:02:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.17 seconds. Powered By: Snitz Forums 2000