[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

PROBLEM: sp_attach_single_file_db

Posted on 2000-04-06
13
Medium Priority
?
1,127 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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

591 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