Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy