Solved

Create ldf file from mdf file

Posted on 2007-03-22
13
6,401 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 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