Solved

PROBLEM: sp_attach_single_file_db

Posted on 2000-04-06
13
1,087 Views
Last Modified: 2012-05-04
Due to some problems I could only save the database MDF file - the LDF file is lost. We also had to reinstall SQL7.

I'm trying to attach the database again:

sp_attach_single_file_db ('VskWin','d:\mssql7\data\VskWin.mdf')

but this fails:

-2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'VskWin' cannot be opened because some of the files could not be activated.

I believe the problem is the missing LDF file - so, is there another way to attach a database from the MDF file only OR is there some tool around to regenerate a LDF from a MDF file...?

Thanks, it's quite urgent.
0
Comment
Question by:SLE
[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
  • 3
  • 2
  • 2
  • +5
13 Comments
 
LVL 1

Expert Comment

by:hkmdj
ID: 2690375
0
 
LVL 2

Author Comment

by:SLE
ID: 2690453
hkmdj,

The two articles did not help...
0
 
LVL 1

Expert Comment

by:hkmdj
ID: 2690493
Sorry that didn't help, maybe this will...

http://support.microsoft.com/support/kb/articles/Q224/0/71.ASP

:-)
0
Technology Partners: 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 2

Author Comment

by:SLE
ID: 2690577
No hkmdj, did you read my question properly? The problem is that I am missing the LDF file...

:-|
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2690934
I have never tried this but have been told it works.


Create a database exactly the same size as the one you lost. Then shutdown the server replace the MDF file with the one you have and restart the server.
0
 

Expert Comment

by:kat1234
ID: 2691667
simonsabin,

I have actually done that on a couple of occasions and it works.

Christina.

0
 

Expert Comment

by:kat1234
ID: 2691670
kat1234 changed the proposed answer to a comment
0
 
LVL 8

Expert Comment

by:chigrik
ID: 2692162
Your syntax is incorrect:
sp_attach_single_file_db ('VskWin','d:\mssql7\data\VskWin.mdf')

The proper syntax is
sp_attach_single_file_db 'VskWin','d:\mssql7\data\VskWin.mdf'

Read about sp_attach_single_file_db from SQL Server Books Online.
It's therefrom:

"
Remarks
When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly-attached database.

Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.
"

So your log file will be automatically recreated.
Look at my test example:

USE master
GO

CREATE DATABASE dbTest_
ON
( NAME = dbTest_dat,
  FILENAME = 'f:\mssql7\data\dbTest_dat.mdf',
  SIZE = 4,
  MAXSIZE = 6,
  FILEGROWTH = 2)
LOG ON
( NAME = dbTest_log,
  FILENAME = 'f:\mssql7\data\dbTest_log.ldf',
  SIZE = 1,
  MAXSIZE = 1,
  FILEGROWTH = 1)
GO

sp_detach_db 'dbTest_', 'true'
GO

sp_attach_single_file_db 'dbTest_', 'f:\mssql7\data\dbTest_dat.mdf'
GO

This is the results set:

"
The CREATE DATABASE process is allocating 4.00 MB on disk 'dbTest_dat'.
The CREATE DATABASE process is allocating 1.00 MB on disk 'dbTest_log'.
Successfully detached database 'dbTest_'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Successfully attached database 'dbTest_'.
"
0
 
LVL 2

Author Comment

by:SLE
ID: 2692392
chigrik, I now about the correct syntax - your solution did not work...

kat1234,

how do you create a database EXACTLY the same size as the one I have? From the Management Console, I can only specify a size in MB (the MDF I have is 3.670.016 bytes)?
0
 
LVL 6

Accepted Solution

by:
crsankar earned 200 total points
ID: 2695674
here is what you need to do


CREATE DATABASE pubsnew
ON PRIMARY (FILENAME = 'c:\mssql7\data\pubsnew.mdf')
FOR ATTACH
GO

this will create a database with just the mdf file available. log file is not required. Will be created automatically.


0
 
LVL 6

Expert Comment

by:crsankar
ID: 2695678
I have tried this out using just the pubsnew.mdf file which I have. I deleted the log file for this database to simulate your situation and it works perfect. Please make sure that you have a copy of your .mdf file stored elsewhere befor trying anything.

GOOD LOUCK!
0
 

Expert Comment

by:rwaai
ID: 8595350
All, welcome to deya-vu.

I'm faced with the same challenge. An MDF file that cannot be attached. I suspect that it was never deattached in the first place (alas, I do not know the history of this file).

Either by using sp_attach_single_file_db, or by using the suggested approach of crsankar, I get the same result:
------------
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'eFFTrackITDB'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Dovico\Trackit\Data\eFFTrackITDB\eFFTrackITDB.ldf' may be incorrect.
------------
For some silly reason the location of the required ldf file seems to be 'baked in' the mdf definition.

Any other thoughts?

Roger.
0
 

Expert Comment

by:maxout6
ID: 8869564
I've got the same error...  database hung due to no file system space, cannot reattach it...
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 61
SQL Select - identify record discrepancies 1 30
Sql case statement to calculate totals 5 33
Need help with a query 14 36
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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