Friday, March 23, 2012

Moving .ldf to new drive, doesn't work gives "Cannot associate files with different datab

I am trying to move a log file to a non-RAID drive. I have detached
the database and made a copy of the .ldf file. I have taken this copy
of the .ldf file and placed it where I want it to be. I would like to
keep the .mdf file where it is located on the RAID drive, but for some
reason I can only reattach if the .ldf file is in the same location as
the .mdf file.
I have then run:
USE MASTER
GO
EXEC sp_attach_db @.dbname = N'Corporate',
@.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
@.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
And it returns:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
Is this possible?The procedure you described should work fine. But I suspect your .ldf file is
not really from that database.
Linchi
"caperneus@.gmail.com" wrote:
> I am trying to move a log file to a non-RAID drive. I have detached
> the database and made a copy of the .ldf file. I have taken this copy
> of the .ldf file and placed it where I want it to be. I would like to
> keep the .mdf file where it is located on the RAID drive, but for some
> reason I can only reattach if the .ldf file is in the same location as
> the .mdf file.
> I have then run:
> USE MASTER
> GO
> EXEC sp_attach_db @.dbname = N'Corporate',
> @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> And it returns:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
> Is this possible?
>|||On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> The procedure you described should work fine. But I suspect your .ldf file is
> not really from that database.
> Linchi
> "capern...@.gmail.com" wrote:
> > I am trying to move a log file to a non-RAID drive. I have detached
> > the database and made a copy of the .ldf file. I have taken this copy
> > of the .ldf file and placed it where I want it to be. I would like to
> > keep the .mdf file where it is located on the RAID drive, but for some
> > reason I can only reattach if the .ldf file is in the same location as
> > the .mdf file.
> > I have then run:
> > USE MASTER
> > GO
> > EXEC sp_attach_db @.dbname = N'Corporate',
> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> > And it returns:
> > Server: Msg 5173, Level 16, State 2, Line 1
> > Cannot associate files with different databases.
> > Is this possible?
It is really from that database. I can attach it back with no problems
if the files are in the same directory.|||Instead of detached and attached try backup and restore ...
BACKUP DATABASE Corporate
TO DISK = 'E:\backup_corporate.bak'
WITH INIT
Drop the database, and delete the mdf and ldf file (or just back it up)
Then the Restore:
RESTORE DATABASE Corporate
FROM DISK = 'E:\backup_corporate.bak'
WITH RECOVERY,
MOVE '[datafilelogicalname]' TO 'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
MOVE '[loglogicalname]' TO 'E:\Web\Corporate\Corporate_Log.ldf'
Hope that works.
Lucas
<caperneus@.gmail.com> wrote in message
news:1181852163.020692.243720@.a26g2000pre.googlegroups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>> The procedure you described should work fine. But I suspect your .ldf
>> file is
>> not really from that database.
>> Linchi
>> "capern...@.gmail.com" wrote:
>> > I am trying to move a log file to a non-RAID drive. I have detached
>> > the database and made a copy of the .ldf file. I have taken this copy
>> > of the .ldf file and placed it where I want it to be. I would like to
>> > keep the .mdf file where it is located on the RAID drive, but for some
>> > reason I can only reattach if the .ldf file is in the same location as
>> > the .mdf file.
>> > I have then run:
>> > USE MASTER
>> > GO
>> > EXEC sp_attach_db @.dbname = N'Corporate',
>> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
>> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
>> > And it returns:
>> > Server: Msg 5173, Level 16, State 2, Line 1
>> > Cannot associate files with different databases.
>> > Is this possible?
> It is really from that database. I can attach it back with no problems
> if the files are in the same directory.
>

No comments:

Post a Comment