Solved

sp_attach_single_file

Posted on 2002-07-23
5
1,704 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
Comment Utility
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
Comment Utility
Hi,

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

Regards
  David
0
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

762 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

12 Experts available now in Live!

Get 1:1 Help Now