Problem reattaching database

21 Dec 2005 10:49 PM

iScanTeam@community.nospam

I am writing a program in vb6 to move a database and its log file from one
directory to another using sp_attach_db and sp_detach_db.
I used 'sp_detach_db mydb' to detach the database, moved it to the new
directory location and then tried to attach the database using the command:
'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
it; however I get the following error message.

"
Run-time error '-2147217900 (80040e14)':

The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
files. A database can have only one primary file.
"
Am I missing a parameter in sp_attach_db?
Is there another step that I am missing that needs to be done before I
attach it again?

21 Dec 2005 11:13 PM

Dan Guzman

> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?

I would guess that you have a bug in your code and accidentally copied the
primary data file (mdf) to both the mdf and ldf file names.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"iScanTeam@community.nospam"
<iScanTeamcommunitynospam@discussions.microsoft.com> wrote in message

Show quote

news:164CA596-73DD-46F5-9590-6EED155B5AF9@microsoft.com...
>I am writing a program in vb6 to move a database and its log file from one
> directory to another using sp_attach_db and sp_detach_db.
> I used 'sp_detach_db mydb' to detach the database, moved it to the new
> directory location and then tried to attach the database using the
> command:
> 'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
> it; however I get the following error message.
>
> "
> Run-time error '-2147217900 (80040e14)':
>
> The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
> files. A database can have only one primary file.
> "
> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
>

22 Dec 2005 12:37 AM

Arvander

My man! Check to make sure your specified log and data files are indeed in
that directory. Otherwise, try something like this.

EXEC sp_attach_db @dbname = N'MyDB',
   @filename1 = N'c:\path\mydb_log.ldf',
   @filename2 = N'c:\path\mydb.mdf'

Show quote

"iScanTeam@community.nospam" wrote:

> I am writing a program in vb6 to move a database and its log file from one
> directory to another using sp_attach_db and sp_detach_db.
> I used 'sp_detach_db mydb' to detach the database, moved it to the new
> directory location and then tried to attach the database using the command:
> 'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
> it; however I get the following error message.
>
> "
> Run-time error '-2147217900 (80040e14)':
>
> The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
> files. A database can have only one primary file.
> "
> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
>