SQL Server Performance

restore database with MDF file only

Discussion in 'General DBA Questions' started by beaniebear, Nov 15, 2005.

  1. beaniebear New Member

    I need to restore a SQL Server database with data file only. I try to run the following command:
    exec sp_attache_single_file_db @dbname = 'TEST'
    @physname="D:ackupcustomer filesMICRO Metadata_Data.MDF'
    However, I get the error message as:
    "Could not open new database 'TEST'. Create Database is aborted.
    Device activation error. The physical file name 'd:mssqldataMICRO Metadata_Data.MDF' maybe incorrect."

    Thanks for your help.

    - Kathy

  2. Haywood New Member

    Was Test originally detached from the source server?
  3. Luis Martin Moderator

    And where is the file? in d:ackup.... or in d:mssql.....?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  4. beaniebear New Member

    It is in D:ackupcustomer filesMICRO Metadata_Data.MDF. I have to copy this file to d:mssqldata... to keep it same as physname path. Is that correct?
    BTW, does this database have to be detached from the source server?

    Thanks,
    - Kathy
  5. Luis Martin Moderator

    Yes, if you want to use attach you have to dettach previusly.
    If there other database running with the same test?.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  6. satya Moderator

    Check the user account has required privileges on the specified path.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. beaniebear New Member

    I use the following way to do it:
    1. I create DB named TEST
    2. I copy the MDF to the location d:dataTEST1_DATA.MDF
    3. exec sp_detach_db @dbname='TEST'
    exec sp_attach_single_file_db @dbname='TEST',
    @physname='d:dataTEST1_DATA.MDF'

    then I got error message: "Create Database aborted. Device aviation error. 'd:mssqldataTEST1_LOG.LDF' maybe incorrect.

    Thanks,
    - Kathy
  8. satya Moderator

    Ensure you've deleted the previous .LDF file from the specified path, as this SP_ATTACH_SINGLE_FILE_DB will recreate Log file afresh and also check the specified path exists.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page