problems reataching a database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

problems reataching a database

Hello.<br /><br />I did a mess with a database, thankfully a test-only one.<br />First I added a log file (teste_opr_log_2.LDF), then I deleted this file. After <br />that the database did not respond; I don´t remember the <br />exact message, and I can´t be sure if this is all the mess<br />I did.<br /><br />After that I managed to detach de database. I hoped to reatach <br />it later without the log file I suspected to be part o problem <br />(the other part was me [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]). <br /><br />Sadly, after trying to reatach the database I´ve got the following errors:<br /><br />Trying to attach the database with the older log file…<br /><br />EXEC sp_attach_db @dbname = N’teste_opr’, <br /> @filename1 = N’c:MSSQL7Data este_opr_Data.MDF’, <br /> @filename2 = N’c:MSSQL7Data este_opr_log.LDF'<br /><br />Server: Msg 5171, Level 16, State 2, Line 1<br />C:MSSQL7Data este_opr_log_2.LDF is not a primary database file.<br />Server: Msg 5105, Level 16, State 1, Line 1<br />Device activation error. The physical file name ‘C:MSSQL7Data este_opr_log_2.LDF’ may be incorrect.<br />Server: Msg 945, Level 14, State 1, Line 1<br />Database ‘teste_opr’ cannot be opened because some of the files could not be activated.<br />Server: Msg 1813, Level 16, State 1, Line 1<br />Could not open new database ‘teste_opr’. CREATE DATABASE is aborted.<br /><br /><br /><br /><br />Then I made a copy of the original log file (teste_opr_log.LDF), name it as the <br />newer, deleted log file (teste_opr_log_2.LDF), and tryed to attach the database with <br />the older log file…<br /><br />EXEC sp_attach_db @dbname = N’teste_opr’, <br /> @filename1 = N’c:MSSQL7Data este_opr_Data.MDF’, <br /> @filename2 = N’c:MSSQL7Data este_opr_log_2.LDF'<br /><br />Server: Msg 5105, Level 16, State 4, Line 1<br />Device activation error. The physical file name ‘C:MSSQL7Data este_opr_log_2.LDF’ may be incorrect.<br />Server: Msg 5105, Level 16, State 1, Line 1<br />Device activation error. The physical file name ‘C:MSSQL7Data este_opr_log_2.LDF’ may be incorrect.<br />Server: Msg 945, Level 14, State 1, Line 1<br />Database ‘teste_opr’ cannot be opened because some of the files could not be activated.<br />Server: Msg 1813, Level 16, State 1, Line 1<br />Could not open new database ‘teste_opr’. CREATE DATABASE is aborted.<br /><br /><br /><br />And the last desperate attempt.<br /><br />sp_attach_single_file_db @dbname = ‘tst_opr’, <br /> @physname = ‘c:MSSQL7Data este_opr_Data.MDF'<br /><br />Server: Msg 5171, Level 16, State 2, Line 1<br />C:MSSQL7Data este_opr_log_2.LDF is not a primary database file.<br />Server: Msg 5105, Level 16, State 1, Line 1<br />Device activation error. The physical file name ‘C:MSSQL7Data este_opr_log_2.LDF’ may be incorrect.<br />Server: Msg 945, Level 14, State 1, Line 1<br />Database ‘tst_opr’ cannot be opened because some of the files could not be activated.<br />Server: Msg 1813, Level 16, State 1, Line 1<br />Could not open new database ‘tst_opr’. CREATE DATABASE is aborted.<br /><br /><br />I hope some of may help me.<br /><br />Bye!
You can try using EM to attach database without log.
EM ask to make a new log. If there is no problem, that would work. HTH Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Thanks for answering Luis. How do I do it in MSSql7?
Using SQl Tools, Enterprise Manager, databases, right click, all task, attach database. Brose to find MDF, in folder, and uncheck log file. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
]]>