Solved

Create ldf file from mdf file

Posted on 2007-03-22
13
6,372 Views
Last Modified: 2008-04-05
I have sql 2000 and unfortunately I have deleted a log file (ldf file) although the mdf file still exists. Since I am unable to reattach an mdf file with a ldf file, how do I go about re creating one from it?

Thanks
0
Comment
Question by:MaximusMeridus
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18771286
sp_attach_single_file_db [ @dbname = ] 'dbname'
    , [ @physname = ] 'physical_name'

0
 
LVL 1

Author Comment

by:MaximusMeridus
ID: 18771304
Where do I type this? In query analyser?

If the database file is named

NDProjects_Data.MDF

how will the command above look?

Thanks
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18771334
type this in query analyser

here is an example

Examples
This example detaches pubs and then attaches one file from pubs to the current server.

EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'



0
 
LVL 1

Author Comment

by:MaximusMeridus
ID: 18771376
After using:-

EXEC sp_attach_single_file_db @dbname = 'NDProjects',
   @physname = 'F:\System Files\MSSQL Data\NDProjects_Data.MDF'

I am getting:-

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'NDProjects'. CREATE DATABASE is aborted.
File activation failure. The physical file name "D:\MSSQL Data\NDProjects_Log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18771516
A trick that may do it...

1) Copy the MDF file in a backup folder
2) Create a database with the same name than the original database (make sure you respect its physical pathes and names) .  The idea here is to try to use a dummy LDF file and the old MDF file
3) Detach the database
4) Delete the new MDF file and replace it with a copy of the old MDF file you have put in the backup folder
5) reattach both the old MDF file and the new LDF file...

Hope this helps...
0
 
LVL 1

Author Comment

by:MaximusMeridus
ID: 18771633
I have done this, thought of this before, but it says it doesn't match of something to that affect.

I get the following error when reattaching:-

"An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details."

There is no hyperlink.....
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Expert Comment

by:rboyd56
ID: 18771657
If that does not work, try this:

NOTE: These are last ditch effort steps and not guaranteed to work, but I have used them in the past to successfully recover from this situation.

Create a new database with the same name and file name
Stop SQL Server and replace the mdf fiel of the new database with the one you have (keep a copy of it)
Start SQL Server
The database will come up suspect
Run these commands in Query Analyzer:

USE MASTER
GO
EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_DBOption '<dbname>', 'SINGLE USER', 'TRUE'
GO
DBCC REBUILD_LOG ('<dbname>', '<path>\<dbname>_Log.ldf')
GO
EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 0WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_dboption '<dbname>', 'SINGLE USER', 'FALSE'
GO

Replace <path> and <dbname> with the full drive/path to where the log file and database name

I would run each step one at a time to verify that each one succeeds.

These steps could expose corruption that might exist in the database. If there is corruption, it is already there. These steps will not cause it, they will just expose it.

So now run DBCC Checkdb(dbname)

Look at the last lines of the output to see if there is any error messages and any inforamtion about what you can do to correct it.

These steps are usually a last ditch effor to get the database operational. They may not work but they will not make the situation worse. Just make sure you keep a backup copy of the mdf file in case this does not work.

You may need to stop and start SQL Server before running checkdb. If the database comes up suspect again then you will need to run these commands to run checkdb

EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Then if checkb allows you correct any corruption problems then you will need to run this batch to get the database fully operational:

EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 0WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 18771863
0
 
LVL 1

Author Comment

by:MaximusMeridus
ID: 18771871
Hi rboyd56

I get the following error when trying to run the first set of commands

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Server: Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Server: Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\MSSQL Data\NDProjects_Data.mdf". Operating system error 5: "5(error not found)".
Server: Msg 945, Level 14, State 1, Line 1
Database 'NDProjects' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.
Server: Msg 2526, Level 16, State 3, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Server: Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Server: Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\MSSQL Data\NDProjects_Data.mdf". Operating system error 5: "5(error not found)".
Server: Msg 945, Level 14, State 1, Line 1
Database 'NDProjects' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18771890
Can you post the commands you used?
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18771919
I would suggest running each step indicividually. It is easier to see what is going on if one of them fails.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18771955
The link that aneeshattingal posted might be easier.
0
 
LVL 1

Author Comment

by:MaximusMeridus
ID: 18772134
Absolutely fantastic! You would this SQL would let you at least look at it in emergency mode. Oh well, it works, got all my data back! :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
Extract XML Data from using TSQL 5 28
Date conversion in sql server 2012 6 25
Sql query 34 17
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now