Results 1 to 4 of 4

Thread: Any SQL Tech smart ppl here?

  1. #1
    Senior Member skozzy's Avatar
    Join Date
    Jan 2008
    Location
    Brisbane
    Age
    55
    Posts
    525
    Thanks
    31
    Thanked 19 Times in 16 Posts
    Rep Power
    216
    Reputation
    114

    Default Any SQL Tech smart ppl here?

    I had the MDF and LDF files from my database recovered from a crashed windows install.

    When I try to Attach them in MS SQL Management Studio in the original folder path I get errors, but if I attach from my Uploads folder it attaches fine and works.
    These files were in a custom folder originally and not in the SQL folders where the server is installed.

    So, c:\uploads\ works for attach but...
    c:\mydatabase\ will not work.

    Since it is attached now and appears complete, how can I get them to c:\mydatabase folder



Look Here ->
  • #2
    Banned

    Join Date
    Feb 2012
    Posts
    2,361
    Thanks
    166
    Thanked 1,205 Times in 607 Posts
    Rep Power
    0
    Reputation
    16611

    Default

    In Windows create a directory junction (also called a soft link), i.e. link C:\uploads to C:\mydatabase

    Read more here:

  • #3
    Senior Member

    Join Date
    Dec 2011
    Location
    Tasmania
    Posts
    869
    Thanks
    1,485
    Thanked 956 Times in 416 Posts
    Rep Power
    544
    Reputation
    18321

    Default

    What error did you see when you tried attaching the database files from C:\mydatabase? Are you running "proper" MS SQL Server, or "SQL Server Express"? Which version of SQL? (2008 R2 / 2012 / 2014)?

    The most likely problem is that SQL does not have permission to write to the C:\mydatabase directory. If it was installed by someone going Next > Next > Next, SQL usually defaults to running as the Local System user and has access to everything. In a perfect World, SQL is always installed to run under a dedicated service account. If this has been done, the service account may not be able to write to c:\mydatabase, since the SQL installer will only grant this service account appropriate permission to access the directories it creates for the database files. To check and fix this:

    1. Open SQL Server Configuration Manager
    2. See if there's a user name next to "SQL Server" in the "Log On As" column
    3. If it's not one of the built in accounts, then grant the account "full control" on c:\mydatabase
    4. Detach your database in Management Studio
    5. COPY the .mdf and .ldf files to c:\mydatabase
    6. The copy ensures that the new permissions are applied to the files
    7. Now attach the two new files
    8. If the DB attaches, right-click the database > Properties > Files - check the path to both files is correct


    If it attaches, do a CHECKDB to make sure everything is working & nothing is corrupt:
    Code:
      DBCC CHECKDB('nameofdatabase')
        WITH NO_INFOMSGS, ALL_ERRORMSGS;
    Last edited by shred; 09-08-15 at 08:34 AM.

  • #4
    Senior Member skozzy's Avatar
    Join Date
    Jan 2008
    Location
    Brisbane
    Age
    55
    Posts
    525
    Thanks
    31
    Thanked 19 Times in 16 Posts
    Rep Power
    216
    Reputation
    114

    Default

    Laptop is running Windows 8.1, I re run the HP System Recovery last night and its finished now, just wasn't happy with the botched up OS repair, will setup SQL Server on it now. So won't be able to say what the error message will be this time. But last night if I remember correctly Unable to open the physical file Operating System Error 5 Access Denied 5120 .... (after that i forget). As for the version of SQL it was Microsoft SQLExpress Server 2012, and Management Studio 2012 Express. Both x64 version.

    I will give a better description of the error when I try attach again after reinstalling these two 2012 Express programs.

    Google searches on the subject say i have the wrong sharing permissions on the folder, but I have never shared the folder on the old windows install.

    And that just gave me an idea which i tried and worked. Right click the MDF and LDF files, properties, security, and gave the username full permissons. And it Attached without errors. Not sure if it was the correct way but will soon tell.

    Update: All working now.
    Last edited by skozzy; 10-08-15 at 01:36 PM.

  • Bookmarks

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •