Re: Missing log file...

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 13:49:42 +0530

Hi,

You might loose the consistency if you delete the LDF file directly.. Since
you are not that bothered bothered about data you could try

1. Try to attach the database using

    EXEC sp_attach_single_file_db @dbname = 'pubs', @physname =
'D:\MSSQL\data\dbname.mdf'

This will attach the database with MDF file and will create a new LDF file.

This command will fail some times, since you have not detached the database
properly.. in that use the below method

2.

  1. Create a new database with the same name and same MDF and LDF files
  2. Stop sql server and rename the existing MDF to a new one and copy the
 original MDF to this location and delete the LDF files.
  3. Start SQL Server
  4. Now your database will be marked suspect
  5. Update the sysdatabases to update to Emergency mode. This will not use
  LOG files in start up

   Sp_configure "allow updates", 1
   go
   Reconfigure with override
   GO
   Update sysdatabases set status = 32768 where name = "BadDbName"
   go
   Sp_configure "allow updates", 0
   go
   Reconfigure with override
   GO

  6. Restart sql server. now the database will be in emergency mode ( You
could access the database)
  7. Use enterprise manager to script all your objects

Thanks
Hari
MCDBA

"Radek" <radek@obecniezwarszawy.pl> wrote in message
news:cfv1kb$4r3$1@nemesis.news.tpi.pl...
> Hi,
>
> OK - I did stupid thing but... ;-)
> I put database offline and then I deleted log file.
> Then I was surprised that database do not want to
> go into on-line status...
> I am interested in object of this db not in data.
> Is there any hope?
> (I know - for me - not, but for this database? ;-)
>
> Radek
>
>

Relevant Pages

  • Re: Recover SQL Database from suspect status
    ... Stop and start the SQL server and try accessing the webc database ... Suspect database may be due to below reasons. ... MDF or LDF files may be used during the SQL Server service startup ... LDF file might be corrupt or immediate power shutdown caused the LDF to ...
    (microsoft.public.sqlserver.server)
  • Re: Having a problem attaching a database
    ... SQL Server Storage Engine Development ... > having trouble attaching the database to pull the required information. ... Select the database that I want to attach (renamed file in new ... Click OK and a message appears asking if I want to create an ldf file. ...
    (microsoft.public.sqlserver.server)
  • Error 1813 or "WHY didnt they call me earlier?"
    ... 3- copy the data file (mdf and ndf's) over the ones from ... 4- start sql server ... >The SQL Server 7.0 database on this server appear after ... >but I get an error telling me that ldf file and mdf file ...
    (microsoft.public.sqlserver.server)
  • Re: Can I replace 120 day Eval with Personal Edition?
    ... Can you install SQL Server 2000 personal edition. ... Copy the MDF and LDF file to correct directory ... MDF and LDF as a database. ...
    (microsoft.public.sqlserver.setup)
  • Error 1813 or "WHY didnt they call me earlier?"
    ... I am sitting as second row SQL Server support. ... The SQL Server 7.0 database on this server appear after ... I have create the directory, and put the mdf file ... but I get an error telling me that ldf file and mdf file ...
    (microsoft.public.sqlserver.server)