attach_db problem

Hi all,
i have a problem with attaching database,
i have 3 files: one for data, one for temporary data and one for log, they
are copied to a new location after being detached from a server.
when i try to attach them again as a new database i get a message :
The files 'D:\DATA.mdf' and 'D:\LOG.ldf' are both primary files. A database
can only have one primary file.
in a attach command i list them according to the information i get from DBCC
checkprimaryfile command executed prior to detaching database
if anyone has any idea what can cause such behaviour
mndrWhat version of sql server are you using?
Can you try something like this
exec master..sp_attach_db
@.dbname = 'yourdbname',
@.filename1= 'd:\Data.mdf',
@.filename2='d:\Data.ndf',
@.filename3='d:\LOG.ldf'
"Malgorzata Ndreu" wrote:

> Hi all,
> i have a problem with attaching database,
> i have 3 files: one for data, one for temporary data and one for log, they
> are copied to a new location after being detached from a server.
> when i try to attach them again as a new database i get a message :
> The files 'D:\DATA.mdf' and 'D:\LOG.ldf' are both primary files. A databas
e
> can only have one primary file.
> in a attach command i list them according to the information i get from DB
CC
> checkprimaryfile command executed prior to detaching database
> if anyone has any idea what can cause such behaviour
> mndr
>
>|||i am using exactly such syntax and have error message,
i am using Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
mndr
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Could you try to post the exact code you are running?
Regards
Steen
Malgorzata Ndreu wrote:
> Hi all,
> i have a problem with attaching database,
> i have 3 files: one for data, one for temporary data and one for log,
> they are copied to a new location after being detached from a server.
> when i try to attach them again as a new database i get a message :
> The files 'D:\DATA.mdf' and 'D:\LOG.ldf' are both primary files. A
> database can only have one primary file.
> in a attach command i list them according to the information i get
> from DBCC checkprimaryfile command executed prior to detaching
> database
> if anyone has any idea what can cause such behaviour
> mndr|||I just found where my problems come from
thank you all for support

mndr
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||this is exact code i am using
DECLARE @.vMDFFile nvarchar(255)
DECLARE @.vDatabaseName nvarchar(255)
DECLARE @.vDestinationPath nvarchar(255)
SET @.vMDFFile = 'c:\zzz\zzz_STA_DATA_20050307154515.mdf'
SET @.vDatabaseName = 'abc'
SET @.vDestinationPath = 'c:\zzz'
SET NOCOUNT ON
DECLARE @.SQL nvarchar(4000)
DECLARE @.FileID tinyint
DECLARE @.FileName nvarchar(255)
IF EXISTS(SELECT NULL FROM tempdb..sysobjects WHERE name LIKE
N'##FilesToAttachListTable' AND xtype = 'u') DROP TABLE
##FilesToAttachListTable
CREATE TABLE ##FilesToAttachListTable (status int, fileid tinyint,
[name] nvarchar(255), [filename] nvarchar(255))
INSERT ##FilesToAttachListTable EXECUTE('DBCC checkprimaryfile (''' +
@.vMDFFile + ''', 3)')
IF EXISTS(SELECT NULL FROM tempdb..sysobjects WHERE name LIKE
N'##FilesToCopyListTable' AND xtype = 'u') DROP TABLE
##FilesToCopyListTable
CREATE TABLE ##FilesToCopyListTable (status int, fileid tinyint, [name]
nvarchar(255), [filename] nvarchar(255))
INSERT ##FilesToCopyListTable EXECUTE('DBCC checkprimaryfile (''' +
@.vMDFFile + ''', 3)')
UPDATE ##FilesToCopyListTable
SET [filename] = REVERSE(LEFT(REVERSE(LTRIM(RTRIM([FileName]))),
CHARINDEX(N'', REVERSE(LTRIM(RTRIM([FileName])))) - 1))
DECLARE FilesToCopyList CURSOR FOR
SELECT [filename] FROM ##FilesToCopyListTable
OPEN FilesToCopyList
DECLARE @.FileToCopy nvarchar(128)
FETCH NEXT FROM FilesToCopyList INTO @.FileToCopy
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = 'execute master.dbo.xp_cmdshell ''copy "' +
LTRIM(RTRIM(@.vMDFFile)) + '" /y "' + LTRIM(RTRIM(@.vDestinationPath +
N'' + @.FileToCopy)) + '"'', no_output'
EXECUTE (@.SQL)
FETCH NEXT FROM FilesToCopyList INTO @.FileToCopy
END
SET @.FileID = (SELECT min(FileID) FROM ##FilesToAttachListTable)
WHILE @.FileID < (SELECT max(FileID) + 1 FROM ##FilesToAttachListTable)
BEGIN
SET @.FileName = (SELECT [FileName] FROM ##FilesToAttachListTable WHERE
FileID = @.FileID)
--SET @.SQL = 'execute master.dbo.xp_cmdshell ''copy "' +
LTRIM(RTRIM(@.FileName)) + '" /y "' + LTRIM(RTRIM(@.vDestinationPath)) +
'"'', no_output'
--EXECUTE (@.SQL)
UPDATE ##FilesToAttachListTable
SET [FileName]= @.vDestinationPath +
REVERSE(LEFT(REVERSE(LTRIM(RTRIM([FileName]))), CHARINDEX(N'',
REVERSE(LTRIM(RTRIM([FileName])))) - 1))
WHERE FileID = @.FileID
SET @.FileID = @.FileID + 1
END
SET @.FileID = (SELECT min(FileID) FROM ##FilesToAttachListTable)
SET @.SQL = 'N''sp_attach_db @.dbname = N'' + @.vDatabaseName + '''
WHILE @.FileID < (SELECT max(FileID) + 1 FROM ##FilesToAttachListTable)
BEGIN
SET @.FileName = (SELECT [FileName] FROM ##FilesToAttachListTable WHERE
FileID = @.FileID)
SET @.SQL = @.SQL + ', @.filename' + cast(@.FileID as nvarchar(2)) + ' =
N'' + LTRIM(RTRIM(@.FileName)) + '''
SET @.FileID = @.FileID + 1
END
SET @.SQL = @.SQL + ''''
--select @.SQL
EXECUTE('EXECUTE (' + @.SQL + ')')
IF @.@.ERROR <> 0
SELECT 0 AS N'STATUS', N'UNSPECIFIED ERROR' AS N'DESCRIPTION'
ELSE
SELECT 1 AS N'STATUS', N'' AS N'DESCRIPTION'
SET NOCOUNT OFF
here is output from checkprimaryfile
status fileid name
filename
-- -- ----
----
32770 1 STA_DATA
C:\zzz\zzz_STA_DATA_20050307154515.mdf
32834 2 STA_LOG
C:\zzz\zzz_STA_LOG_20050307154515.ldf
32770 3 STA_TMP
C:\zzz\zzz_STA_TMP_20050307154515.ndf
end sql statement executed by server as final attach
returned from SQLProfiler
sp_attach_db @.dbname = N'abc', @.filename1 =
N'c:\zzz\zzz_STA_DATA_20050307154515.mdf', @.filename2 =
N'c:\zzz\zzz_STA_LOG_20050307154515.ldf', @.filename3 =
N'c:\zzz\zzz_STA_TMP_20050307154515.ndf'
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||...and what was the problem?
/Steen
mndr mndr wrote:
> I just found where my problems come from
> thank you all for support
>
> mndr
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!