[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PROBLEM: sp_attach_single_file_db

Posted on 2000-04-06
13
Medium Priority
?
1,115 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 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