Solved

PROBLEM: sp_attach_single_file_db

Posted on 2000-04-06
13
1,075 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
Comment Utility
0
 
LVL 2

Author Comment

by:SLE
Comment Utility
hkmdj,

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

Expert Comment

by:hkmdj
Comment Utility
Sorry that didn't help, maybe this will...

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

:-)
0
 
LVL 2

Author Comment

by:SLE
Comment Utility
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
Comment Utility
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
Comment Utility
simonsabin,

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

Christina.

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:kat1234
Comment Utility
kat1234 changed the proposed answer to a comment
0
 
LVL 8

Expert Comment

by:chigrik
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I've got the same error...  database hung due to no file system space, cannot reattach it...
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now