Loading ...
Sorry, an error occurred while loading the content.

: datafile attach without LDF file

Expand Messages
  • Deepak Garg
    Hi All, I am facing a problem in SQL 2005, some one deleted the ldf file of one of the databases, after detaching the database. MDF file is available on the
    Message 1 of 4 , Feb 29, 2008
    • 0 Attachment
      Hi All,

      I am facing a problem in SQL 2005, some one deleted the ldf file of one of the databases, after detaching the database. MDF file is available on the disk. i have tried a couple of below options and from GUI also but no luck...

      1) EXEC sp_attach_db @dbname = N'Stage_VertesBI', @filename1 = 'E:\BI Solution\SQL Data\Stage_VertexBI.mdf'

      2) sp_attach_dbfile 'Stage_VertesBI', 'E:\BI Solution\SQL Data\Stage_VertexBI.mdf'

      3) sp_attach_single_file_db @dbname= 'Stage_VertesBI' ,@physname= 'E:\BI Solution\SQL Data\Stage_VertexBI.mdf'

      but getting the same error:

      File activation failure. The physical file name "E:\BI Solution\SQL Data\Stage_VertexBI_1.ldf" may be incorrect.
      The log cannot be rebuilt because the database was not cleanly shut down.
      Msg 1813, Level 16, State 2, Line 1
      Could not open new database 'Stage_VertesBI'. CREATE DATABASE is aborted.

      A help from someone would be highly appreciated.

      Thanks in advance.

      With regards,
      Deepak


      ---------------------------------
      Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

      [Non-text portions of this message have been removed]
    • J Santhoshkumar
      Hey Deepak, Try this solution. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19030 With :))s Santhosh =====-----=====-----===== Notice: The information
      Message 2 of 4 , Mar 1, 2008
      • 0 Attachment
        Hey Deepak,


        Try this solution. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19030



        With :))s

        Santhosh

        =====-----=====-----=====
        Notice: The information contained in this e-mail
        message and/or attachments to it may contain
        confidential or privileged information. If you are
        not the intended recipient, any dissemination, use,
        review, distribution, printing or copying of the
        information contained in this e-mail message
        and/or attachments to it are strictly prohibited. If
        you have received this communication in error,
        please notify us by reply e-mail or telephone and
        immediately and permanently delete the message
        and any attachments. Thank you
      • Deepak Garg
        Hi Santhosh, Thanks for your help. but i am getting below error while updating sysdatabases in the 7th step of document: 7) Open Query Analyser and in master
        Message 3 of 4 , Mar 3, 2008
        • 0 Attachment
          Hi Santhosh,

          Thanks for your help.
          but i am getting below error while updating sysdatabases in the 7th step of document:


          7) Open Query Analyser and in master database run the following :
          sp_configure 'allow updates',1
          go
          reconfigure with override
          go
          update sysdatabases set
          status=-32768 where dbid=DB_ID('fake')
          go
          sp_configure 'allow updates',0
          go
          reconfigure with override
          go

          **Msg 259, Level 16, State 1, Line 1
          **Ad hoc updates to system catalogs are not allowed.

          Please suggest.

          Thanks,
          deepak

          J Santhoshkumar <j.santhoshkumar@...> wrote:
          Hey Deepak,

          Try this solution. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19030

          With :))s

          Santhosh

          =====-----=====-----=====
          Notice: The information contained in this e-mail
          message and/or attachments to it may contain
          confidential or privileged information. If you are
          not the intended recipient, any dissemination, use,
          review, distribution, printing or copying of the
          information contained in this e-mail message
          and/or attachments to it are strictly prohibited. If
          you have received this communication in error,
          please notify us by reply e-mail or telephone and
          immediately and permanently delete the message
          and any attachments. Thank you






          ---------------------------------
          Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

          [Non-text portions of this message have been removed]
        • Madhu K Nair
          I think you have sql 2005, in that case you can not update system objects. Try these steps (a) create a database with the same name in another directory as
          Message 4 of 4 , Mar 3, 2008
          • 0 Attachment
            I think you have sql 2005, in that case you can not update system
            objects. Try these steps

            (a) create a database with the same name in another directory as the
            one you're trying to attach
            (b) re-create all filesgroups and files as necessary
            (c) shutdown the server
            (d) swap in the old mdf file and any ndf files
            (e) bring up the server and let the database attempt to be recovered
            and then go into suspect mode
            (f) put the database in single_user and emergency modes
            (g) run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will
            rebuild the log and run full repair
            (h) return database to online, multi_user mode

            Madhu


            --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@...> wrote:
            >
            > Hi Santhosh,
            >
            > Thanks for your help.
            > but i am getting below error while updating sysdatabases in the
            7th step of document:
            >
            >
            > 7) Open Query Analyser and in master database run the following :
            > sp_configure 'allow updates',1
            > go
            > reconfigure with override
            > go
            > update sysdatabases set
            > status=-32768 where dbid=DB_ID('fake')
            > go
            > sp_configure 'allow updates',0
            > go
            > reconfigure with override
            > go
            >
            > **Msg 259, Level 16, State 1, Line 1
            > **Ad hoc updates to system catalogs are not allowed.
            >
            > Please suggest.
            >
            > Thanks,
            > deepak
            >
            > J Santhoshkumar <j.santhoshkumar@...> wrote:
            > Hey Deepak,
            >
            > Try this solution. http://www.sqlteam.com/forums/topic.asp?
            TOPIC_ID=19030
            >
            > With :))s
            >
            > Santhosh
            >
            > =====-----=====-----=====
            > Notice: The information contained in this e-mail
            > message and/or attachments to it may contain
            > confidential or privileged information. If you are
            > not the intended recipient, any dissemination, use,
            > review, distribution, printing or copying of the
            > information contained in this e-mail message
            > and/or attachments to it are strictly prohibited. If
            > you have received this communication in error,
            > please notify us by reply e-mail or telephone and
            > immediately and permanently delete the message
            > and any attachments. Thank you
            >
            >
            >
            >
            >
            >
            > ---------------------------------
            > Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
            Try it now.
            >
            > [Non-text portions of this message have been removed]
            >
          Your message has been successfully submitted and would be delivered to recipients shortly.