InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:44:17 GMT20http://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspx[quote][b]GilaMonster (1/11/2012)[/b][hr][quote][b]Perry Whittle (1/11/2012)[/b][hr]To re attach a database with a missing log file just use either[code="SQL"]sp_attach_single_file_db [/code][code="SQL"]CREATE DATABASE .... FOR ATTACH_REBUILD_LOG[/code][/quote]Which only works if the database was shut down cleanly before the log was deleted, not in all cases.[/quote]But it's logically the first place to start, why go to all the effort of hacking the file in if a simple attach may suffice.Thu, 12 Jan 2012 05:54:58 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxIt's a long shot and it doesn't always work but I've had some success in the past with such a thing. 1. Make sure the database in question is detached and that the MDF file is in a safe place. "Safe place" means NOT on the drive that you normally store your MDF files, in this case.2. Create a new database with the same name as the database in question. Note that the logical and physical file names should match the old ones.3. Properly detach the new database.4. Copy (not move) the old MDF file from Step 1 over the new MDF file created in Steps 2 and 3.5. Try to reattach the new database.It doesn't always work but the answer is always "No" unless you "ask".Thu, 12 Jan 2012 05:02:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspx[quote][b]jarupan (1/11/2012)[/b][hr]Will this way work in all cases? [/quote]No. It will only work if the database was cleanly shut down before the log was deleted/removed/not included. Shutting down SQL does not guarantee that the database will have been cleanly shut down (though SQL will try). Detach and offline first are safer, but it's still possible to get a DB that's not cleanly shutdown afterwards (full log is the easiest way to get that)If the database was cleanly shut down a simple attach will work. If it wasn't, attach will fail and you'd have to hack the DB in and do an emergency mode repairWed, 11 Jan 2012 08:05:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxHi Gail Just curious about your comment"Which only works if the database was shut down cleanly before the log was deleted, not in all cases" If I do.1) Copy mdf out (suppose I can stop SQL server).2) Restart SQL server.3) Delete that database4) Copy step#1 to normal location.4) use GUI to attach the database with remove log file out (it can not find it anyhow)(I always use this way to attached learning database, normally it will not come with ldf).Will this way work in all cases? JJ[u]Note[/u]: Thanks you for all your topics and comments, I always learn from you.Wed, 11 Jan 2012 07:58:13 GMTjarupanhttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspx[quote][b]Perry Whittle (1/11/2012)[/b][hr]To re attach a database with a missing log file just use either[code="SQL"]sp_attach_single_file_db [/code][code="SQL"]CREATE DATABASE .... FOR ATTACH_REBUILD_LOG[/code][/quote]Which only works if the database was shut down cleanly before the log was deleted, not in all cases.Wed, 11 Jan 2012 06:57:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspx[quote][b]martin.kerr 34088 (1/11/2012)[/b][hr]Hi,I found this from googleing my problem and it worked!Create database with same name as MDF file you have.Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.Start SQL Server. Database will be now in suspect state because log file is not correct.Run the following script: USE [master] GO ALTER DATABASE [MyDatabase] SET EMERGENCY GO ALTER DATABASE [MyDatabase] SET SINGLE_USER GO DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS) GO ALTER DATABASE [MyDatabase] SET MULTI_USER GO ALTER DATABASE [MyDatabase] SET ONLINE GOThanks anyway[/quote]To re attach a database with a missing log file just use either[code="SQL"]sp_attach_single_file_db [/code][code="SQL"]CREATE DATABASE .... FOR ATTACH_REBUILD_LOG[/code]Wed, 11 Jan 2012 06:55:05 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxWhat you did there was the absolute, very, final last resort for this kind of problem. You could have transactional inconsistencies, you could have structural inconsistencies.Stuff that needs doing:Run CheckDB with no_infomsgs and all_errormsgs and hope there are no errorsSort out your log maintenance. See - [url=http://www.sqlservercentral.com/articles/64582/]Managing Transaction Logs[/url]Also, see the consequences for deleting a log: [url]http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/[/url]Wed, 11 Jan 2012 06:40:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxTo be fair, it wasnt critical data and i could have rebuilt manually but would have taken alot longer than using that script.Wed, 11 Jan 2012 06:34:06 GMTmartin.kerr 34088http://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxReally?Do you understand what each step of that procedure does?If so, would you still recommend it?Wed, 11 Jan 2012 06:30:10 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxHi,I found this from googleing my problem and it worked!Create database with same name as MDF file you have.Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.Start SQL Server. Database will be now in suspect state because log file is not correct.Run the following script: USE [master] GO ALTER DATABASE [MyDatabase] SET EMERGENCY GO ALTER DATABASE [MyDatabase] SET SINGLE_USER GO DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS) GO ALTER DATABASE [MyDatabase] SET MULTI_USER GO ALTER DATABASE [MyDatabase] SET ONLINE GOThanks anywayWed, 11 Jan 2012 06:23:51 GMTmartin.kerr 34088http://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxRestore from backup is probably the best option here. The transaction log is not an optional piece of the database and you can't expect to just delete it with no effects.p.s. Where did that ldf mentioned come from? An earlier date?Wed, 11 Jan 2012 06:08:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1233912-391-1.aspxHi All,I had deleted my .ldf as it was getting far too large! and then when i went back to use that database i recieve the following error when trying to create a new database with the existing .mdfMsg 5173, Level 16, State 1, Line 1One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.Log file 'H:\Logs\360MetaVerse_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.Msg 1813, Level 16, State 2, Line 1Could not open new database '360MetaVerse'. CREATE DATABASE is aborted.Thanks in advanceWed, 11 Jan 2012 05:58:54 GMTmartin.kerr 34088