Solved

sp_attach_single_file

Posted on 2002-07-23
5
1,708 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now