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

Moved SQL to the new box

Hi everybody,

I re-built SQl server 2000 on the new box. Attached and detached all DBs (master, msdb, users DBs) and moved them to the new paths (E:\mssql). Everything is fine.

The questions are:
1. The 'filename' in the master.sysdatabases for 'master' DB is still pointed to the old location (F:\) even physicaly files are on E:\ drive;

2. The 'phyname' in the master.sysdevices for all devices (DBs), including master, msdb, model are still shows the old location (F:\);
At list I will get a wrong reports of the files location.

it could be other places in the system tables that keep old information.
How and where I can correct the information?

3. All jobs, bakups, etc. that creates output files still pointed to the old locations (let say to the F:\sql2000\). F:\ drive is not exists anymore.
I will recreate all jobs and main plans to specify correct path.
But how can I change the DEFAULT locations for all backups, logs, etc. ?

Thank you,
LYOne way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps|||In the rebuild situation, I generally will not restore or reattach system databases, only the user databases. Because my past experience of having numerous troubles with the master after restoring to a new machine. Scheduled tasks and DTS can be saved and transfer to the new machine.

Good luck!|||Originally posted by dbabren
One way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps

=====

All user DB's are fine.
The only incorrect information for master.sysdatabases.filename (for master database) and master.sysdevices (for all devices).

Thank you.
LYsql