PROBLEM: sp_attach_single_file_db

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.
LVL 2
SLEAsked:
Who is Participating?
 
crsankarConnect With a Mentor Commented:
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
 
SLEAuthor Commented:
hkmdj,

The two articles did not help...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

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

:-)
0
 
SLEAuthor Commented:
No hkmdj, did you read my question properly? The problem is that I am missing the LDF file...

:-|
0
 
simonsabinCommented:
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
 
kat1234Commented:
simonsabin,

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

Christina.

0
 
kat1234Commented:
kat1234 changed the proposed answer to a comment
0
 
chigrikCommented:
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
 
SLEAuthor Commented:
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
 
crsankarCommented:
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
 
rwaaiCommented:
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
 
maxout6Commented:
I've got the same error...  database hung due to no file system space, cannot reattach it...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.