Post reply

Attaching the Database

  • February 23, 2005 at 12:48 am

    #67063

    Hi,

    I want to attach the database thru Enterprise Manager, but i have only .mdf file. i dont have the .ldf file. so how to attach the database with out ldf file. if possible u can give thru sql query also.

    when i am attaching the database, i'm getting the following error.

    "Error 1813: Could not open new Database 'Student'. Create Database is aborted. Device activation error. The physical File name 'E:\Folder\student.ldf' may be incorrect."

    Can u pls tell me what is the problem actually.

    Thanks in advance

     

  • February 23, 2005 at 2:03 am

    #543633

    Hi,

    what is the command you are using to attach. You may try this one :

    EXEC sp_attach_single_file_db @dbname = 'dbname', 

    @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

    @physname will have the entire path for the .mdf file

    Hope this helps.

    Kishore

     

  • February 23, 2005 at 3:18 am

    #543642

    With out .ldf file, database can't be attached.

  • February 23, 2005 at 3:44 am

    #543647

    Woah, stop right there.

    Yes it can be attached without a log file.  Kishore is correct in stating that sp_attach_single_file_db can be used to attach an .mdf file without a matching .ldf(s), I've done it myself a number of times. Sometimes it is quite useful to do this, especially if you have to move databases across servers and have a large log file which you don't want to move. sp_attach_single_file_db is the way to do it, and when it is executed, it will build a new log file for the database. Not totally sure, but you may only be able to do this through Query Analyzer - Enterprise Manager may let you do it, although I may have not spotted it.

    Take a look in Books Online for more details, just search for  sp_attach_single_file_db, it's a very useful source of knowledge.


    Jon

  • February 23, 2005 at 5:17 am

    #543660

    Hi....You can also attach it using EM. In EM, Under Databases -- right click ----All Tasks -- Attach Database. Here we specify the .mdf file to be attached and specify the owner for the database. If we have a valid .mdf file, it gets attached and it automatically creates a .ldf file for the database in the same path where your .mdf file is located.

    Thanks.

    --Kishore

  • February 23, 2005 at 5:49 am

    #543663

    Hi Grasshopper, thank u for helping.

    Actually i tried with enterprise manager like u said, while i'm doing like that only i got the error which i already posted.

    Actually the database is student, which is from other server i'm attaching in my machine thru EP. Then i got the following error.

    Actually i placed the database in C:\ProgramFiles\MicrosoftSqlServer\mysql\data folder.

    "Error 1813: Could not open new Database 'Student'. Create Database is aborted. Device activation error. The physical File name 'E:\Folder\student.ldf' may be incorrect."

    Even i tried with the command which u said in query analyzer. the i got the folloiwng error.

    Could not open new database 'STUDENT'. CREATE DATABASE is aborted.

    Device activation error. The physical file name 'E:\mgDatabases\Folder\student_log.ldf' may be incorrect.

    What is the problem.

    thanks

     

  • February 23, 2005 at 7:25 am

    #543686

    Hi,

    Where did you the get the .mdf file from ? I mean was it copied from some other server ? And how was it copied ? Was the SQL Server stopped when the .mdf fiel was copied.

    Provide me with the following details to help you better.

    --Kishore

     

  • February 23, 2005 at 7:36 am

    #543692

    Hi,

    u r right, In the other server the database has been detached and then sent it to me. so now to work in my server i have to attach it.

    thiru

  • February 23, 2005 at 7:46 am

    #543698

    Try copying the .mdf file to soem other location and then attaching it using SQL EM.

    I dont see any other issues as it has been detached and then sent to you.

    It shoule be working fine under normal circumstances.

    --Kishore

  • February 23, 2005 at 11:54 am

    #543751

    Thanks Kishore, I didn't know you could do it through EM, I'd always used QA previously. As an aside, it's not possible to even copy an .mdf/.ldf of an attached database when the MSSQLSERVER service is running, as the database engine will lock the file to protect it from corruption. You have to detach it from the engine first, hence sp_detach_db.  The file locking is also the reason why third party backup software needs a special agent to backup SQL Server database files, even if there is no activity on the database it is not possible to access the files as SQL Server takes an exclusive (file system) lock on them.

    Thiraumalarajkumar, just an aside, are you certain that the server to which you are re-attaching the .mdf is running at least the same or later version of SQL Server as the one where the file came from?  Not certain this is the case here, but the error message suggests that SQL Server can't access or mount it - even if it's not, it's something that's worth keeping in mind when moving databases across servers, whether with sp_detach_db or with backup/restore.


    Jon

  • March 14, 2005 at 5:18 am

    #546804

    In simple terms SQL can't find the log file.  This is because the machine that did the sp_detach_db has a different internal physical file system, and the mdf contains a pointer to where it thinks the ldf should be.  If that physical directory can't be found then it fails.

    For example if the file was detached on a system where the data file was in say:

    d:\Program Files\Microsoft SQL Server\MSSQL\Data\x.mdf

    But you are reattaching to:

    c:\Microsoft SQL Server\MSSQL\Data\x.mdf

    ...then this error will happen.

    You need to get the data and log files and use sp_attach_db.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply