Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Urgent -- need to attach database but don't have ldf

Posted on 2004-11-19
7
Medium Priority
?
275 Views
Last Modified: 2008-02-01
A client sent me xxxx.mdf without the associated xxxx.ldf.  Is there any way to go ahead and attach this database?

I tried creating my own xxxx database, which of course created an xxxx.ldf, then detached it.  Then I tried to attach the correct xxxx.mdf w/the newly created xxxx.ldf, and of course that didn't work.

Can this be done?

0
Comment
Question by:gary_j
6 Comments
 
LVL 97

Assisted Solution

by:Lee W, MVP
Lee W, MVP earned 375 total points
ID: 12630301
This may have an answer for you if you have or acquire an account:

Attaching a database that has missing log file
http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid604510_tax294863,00.html
0
 
LVL 6

Accepted Solution

by:
acampoma earned 375 total points
ID: 12630370
Attach the database using sp_attach_db (see BOL for syntax). If you are lucky, SQL will re-create the ldf file for you.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 375 total points
ID: 12633179
use

sp_attach_single_file_db [@dbname =] 'dbname',
    [@physname =] 'physical_name'

for cases when you don't have the ldf ...

don't forget you still need to sort out the "orphaned" users once the database is created on your server.

hth
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Author Comment

by:gary_j
ID: 12644533
using this command:

sp_attach_single_file_db @dbname = 'mydb',
      @physname = 'e:\sqldata\mydb.mdf'

returns this error:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'mydb'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'c:\sqllogs\mydb.ldf' may be incorrect.

Seems it's looking for the ldf on its own ...
0
 
LVL 6

Assisted Solution

by:pedros7
pedros7 earned 375 total points
ID: 12653461
Unfortunately, those functions only work if you user the dettach function before on the server!

Have a look at:
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html 
0
 
LVL 5

Author Comment

by:gary_j
ID: 12875475
I split the points because the correct answer may well have been "it can't be done" but everybody tried.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

571 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