AttachDBFileName refers to original log file

Hello,

I'm using the AttachDBFilename option in the SQL connection string to attach
a database mdf file copied to another machine.

There is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:

Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Database\Strategic.TimeMaster.Database.SQL2005\TimeMasterDat_log.LDF" may be incorrect

How can I get the attach to avoid referring to this log file?

Coudl you please post your connection string here ? Seems that you are mixing up the setting for server / user instances.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Here it is:

Data Source=.\SQLExpress;AttachDBFileName=C:\Program Files\Time Master\TimeMasterDat.mdf;Initial Catalog=TimeMaster;Trusted_Connection=yes;

The mdf file should be attached to a local instance of SQLExpress. The mdf has been copied from my development machine and the it seems to think that this mdf file is refering to this log file.

It shouldn't, it should create a log file on the local machine.

|||Try to add the "User Instance=true;" to the connectionstring.|||

That worked, but creating another User Instance was not really what I was looking to do, but will probably do

Thanks

|||Did you want to attach the database to the server for a server instance ? or did you want to use the user instance feature of SQL Express ? If you want to use the user instance you WILL have to create one instance per database.

-Jens.|||A server instance I suppose. The server and client being on the same machine.|||

If you want to use a server attached instance you will have to attach the database first (if you have no log LDF file you will have to try to attach the database using sp_attach_single_file_db) using sp_attachdb or the equivalent GUI action in the managment studio.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

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
mndr
What 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 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 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!

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 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
>
>|||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

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!