Re: Lost Log File !

Hi Joh,

Did you tried to access the database after seting the Emergency mode
(32768). If the database is accessible after that then you could do the
steps I specified earlier. Otherwise try to do below:-

sp_attach_single_file_db [ @dbname = ] 'dbname' , 'MDF_FILE'

again if the atatch fails then try:-

Create a new database with same MDF and LDF name

stop sql server and delete the new MDF file and copy the old MDF file

Start SQL Server and see the status.

In both the above approches the data integrity will be lost. So it is
recommended to contact Microsoft PSS or restore from a good backup file.

Thanks
Hari
SQL Server MVP





"Joh" <joh@xxxxxxxxxxxx> wrote in message
news:egawxa$VFHA.2072@xxxxxxxxxxxxxxxxxxxxxxx
> Hari, thanks for your reply but how can I move the data and objects into
> new
> database .... I only have a .mdf file and I want to recover that .mdf ...
> once I will recover then I can move the data and objects from there into
> new
> database.
>
> Right ?
>
> "Hari Prasad" <hari_prasad_k@xxxxxxxxxxx> wrote in message
> news:#lK9wX$VFHA.2796@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi,
>>
>> DBCC Rebuild_Log is not a documented method and it is not at all
>> recommended. So even if it succeeds there will be data consistency issue.
>>
>> So the best approach is :-
>>
>> 1. Create a new database
>> 2. Use DTS to move data and objects to new database
>>
>> If the above steps fail then contact Microsoft product support.
>>
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Joh" <joh@xxxxxxxxxxxx> wrote in message
>> news:%23u86So9VFHA.3760@xxxxxxxxxxxxxxxxxxxxxxx
>> > Here is the following stuff which I do for recovery log file ...
> basically
>> > I
>> > have lost log file and I only have a .mdf file.
>> >
>> > Before going to the DOS mode I update the status of Health Database to
>> > 32768
>> > and I only have a .mdf file.
>> >
>> > From the DOS mode I used the following command
>> >
>> > sqlservr -c -T3608 -T4022
>> >
>> > after that run the following command in Query Analyzer
>> >
>> > select name, status from sysdatabases where name = 'Health'
>> >
>> > Output:-
>> > name status
>> > Health 32768
>> > Go
>> > dbcc traceon(3604)
>> > Go
>> > DBCC Rebuild_Log('Health','C:\Health_Log.ldf')
>> > GO
>> >
>> > Error:-
>> > Server: Msg 17, Level 16, State 1, Line 0
>> > [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not
>> > exist
>> > or access denied.
>> > Server: Msg 10061, Level 16, State 1, Line 0
>> > [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
>> > (Connect()).
>> >
>> > Connection Broken
>> >
>> > When I check from the dos mode it gave the following error:-
>> > 2005-05-13 11:26:16.01 spid51 Could not open FCB for invalid file ID
> 0
>> > in
>> > database 'Health'..
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • Re: "Hidden" Replication Stored Procedures?
    ... Since the engine has hooks that hide the mssqlsystemresource database from ... 4.)Start the SQL Server service; ... Chris Jones ... aforementioned MDF file in Notepad and doing a quick search, ...
    (microsoft.public.sqlserver.replication)
  • Re: Error 823
    ... Regards. ... Microsoft SQL Server Storage Engine ... > I cannot extract much data out of the database and I can't view the most ... The problem is in the .MDF file, ...
    (microsoft.public.sqlserver.tools)
  • Re: ASPNETDB.MDF when no SQLExpress, only SQL Server 2005?
    ... You will need to upload the mdf file and have your webhost attach it to the sql server. ... In which case you will need to recreate the database into the database which is available to the hosted domain if you have signed up ... The connection will be closed. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Lost Log File !
    ... Create a new database with same MDF and LDF name ... stop sql server and delete the new MDF file and copy the old MDF file ... Start SQL Server and see the status. ... > Create a new database with same MDF and LDF name ...
    (microsoft.public.sqlserver.clients)
  • Re: "Hidden" Replication Stored Procedures?
    ... aforementioned MDF file in Notepad and doing a quick search, ... Is there a way to have the Resource Database appear in the list of Databases ... Chris Jones ...
    (microsoft.public.sqlserver.replication)