Solved

Create ldf file from mdf file

Posted on 2007-03-22
13
6,408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SCOM to SQL port 14xx failed? 1 32
SQL Server Sum Over Multiple Tables 20 35
Convert VBA UDF to SQl SERVER UDF 4 52
Neglected Questions 3 13
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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