Create ldf file from mdf file

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
LVL 1
MaximusMeridusAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sp_attach_single_file_db [ @dbname = ] 'dbname'
    , [ @physname = ] 'physical_name'

0
 
MaximusMeridusAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
MaximusMeridusAuthor Commented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
MaximusMeridusAuthor Commented:
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
 
rboyd56Commented:
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
 
MaximusMeridusAuthor Commented:
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
 
rboyd56Commented:
Can you post the commands you used?
0
 
rboyd56Commented:
I would suggest running each step indicividually. It is easier to see what is going on if one of them fails.
0
 
rboyd56Commented:
The link that aneeshattingal posted might be easier.
0
 
MaximusMeridusAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.