Solved

sp_attach_single_file

Posted on 2002-07-23
5
1,714 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:karnaini
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
spcmnspff earned 100 total points
ID: 7174521
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
 
LVL 35

Expert Comment

by:David Todd
ID: 7179366
Hi,

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

Regards
  David
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7179961
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
 

Expert Comment

by:CleanupPing
ID: 9280071
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 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