PROBLEM: sp_attach_single_file_db

Two articles for you to scan:

http://support.microsoft.com/support/kb/articles/q257/8/52.ASP

and

http://support.microsoft.com/support/kb/articles/Q236/9/89.ASP

Hope that helps.

hkmdj,

The two articles did not help...

Sorry that didn't help, maybe this will...

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

:-)

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

:-|

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.

simonsabin,

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

Christina.

kat1234 changed the proposed answer to a comment

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_'.
"

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)?

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!

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.

I've got the same error...  database hung due to no file system space, cannot reattach it...