Restore mdf file without ldf in SQL Server 2005
We had some disk space issues on one of the drives and the reason was because one sql log file had 611 GB of disk space used, so I stopped the service and deleted the file hopeing that creating a similar database with the same name can generate an empty log file that I can use, but this was a mistake, so I searched around for a solution and most of the articles out there was kind of outdated, it mentiones solutions that are not supported anymore by sql server 2005, like changing system tables which is not supported anymore, so here is the solution:
--move the mdf file to a temporary directory, and create a new database with the same name, point it to use an existing mdf file
Create database
for attach_rebuild_log
and this did the trick for me, the output was little weired:
File activation failure. The physical file name "W:\SQLServerData\myoldLogFile.ldf" may be incorrect.
New log file 'W:\SQLServerData\temp\newLogFile_log.LDF' was created.
And because you probably did this operation using a temporary location, you need to move the files, you can restart sql server service, detach the database, and move the files whereever you want, and then attach from the new location
it works!
