sp_attach_single_file

Hi All,

I'm trying to recover my database server (SQL 7.0) using sp_attach_single_file store procedure.

  EXEC sp_attach_single_file_db @dbname = 'myData',
    @physname = 'c:\mssql7\data\myData.mdf'

and I got this message :
    Database 'myData' cannot be opened because some
    of the files could not be activated.


Is anyone know how to solve my problem?

Regards,
Iskandar
karnainiAsked:
Who is Participating?
 
spcmnspffConnect With a Mentor Commented:
Make sure that you don't have any other log or secondary files that were once associated with that database in the same path that they were once before.  If so rename the log are secondary files to *_old.* or something and try it again.

Here's what happens: sp_Attach_DB and sp_Attach_Single_file_DB are really not much different when it comes to attaching with one file. It ammounts to a create database statement with a "For Attach" option.  In either case if the create database for attach statement is issued while listing a primary file without a log file  (which can occur in both sp_Attach_db and sp_Attach_Single_file_DB) SQL server looks in the primary file's sysfiles table, if it doesn't find the files that are listed there, it creates a new log file, and then  creates the database while attaching the specified primary file.  

In your case, sql server is locating an old log file  and trying to attach it with your primary file. But the old log file doesn't match and an error occurs.

This also brings to light the fact that sp_attach_single_file_db doesn't actually do what it is advertised as.  The same thing can essentialy be accomplished with sp_attach_db by specifying only one master file and having no associated files in the old path . . .

Did I make that clear?
 
0
 
David ToddSenior DBACommented:
Hi,

Also try the sp_attach_db and specify just the file you have.

Regards
  David
0
 
spcmnspffCommented:
Right, as I said, that works because both statements sre essentially the same, but niether statement will work if SQL server finds an log or sencondary file whose name is listed in the orimary file.  Rename the old files first.
0
 
CleanupPingCommented:
karnaini:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
All Courses

From novice to tech pro — start learning today.