locked
Cannot see backup files RRS feed

  • Question

  • I recently added a new physical drive to our SQL 2008 64-bit server.  I created a new folder to hold backup files and copied the files from one of the older drives.  When I go to do a restore on one of the test databases and go to locate backup file (using SSMS) the files in that folder do do show up.  If I look in the previous drive they show.  What am I missing?  Thanks.

    Thursday, September 8, 2011 2:26 PM

Answers

  • Note that when SQL Server accesses the folder and files, it uses the SQL Servewr service account, not whoever you might be logged in as. Important distinction. So, login as the Service Account, and try to access the files. If that fails, you need to work out the permission at the OS level.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Proposed as answer by Stephanie Lv Thursday, September 15, 2011 12:33 PM
    • Marked as answer by Stephanie Lv Monday, September 19, 2011 11:19 AM
    Friday, September 9, 2011 6:00 PM

All replies

  • NTFS permissions?  Does your SQL Server service account have access to those directories?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Thursday, September 8, 2011 3:42 PM
  • Did you re-start SQL Server since adding that drive? I believe that sQL Server sees what drives existed at startup. It might be a GUI thing, though, meaning that it might be available if you type your RESTORE commands instead of "clicking the GUI".
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, September 8, 2011 4:45 PM
  • It could be several things, however see if you can see them from a DOS Shell. Start/ Run/ CMD

    do a dir <path to files>

    and see if they are listed, let me know.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Thursday, September 8, 2011 9:54 PM
  • I can see the drive and the folder where the backup files are stored.  I just cannot see the backup files when using the GUI.  I tried the RESTORE command from the query window and it said it could not find the file.  However, I did get an Error 5 (Access is denied).  This is strange though because I have the exact same security settings on the old folder in the D drive and those files show up just fine.
    Friday, September 9, 2011 1:37 PM
  • ok, well it sounds like it could be NTFS perms. Try login on with a administrator account and see if they are visible to that, if so then it is definitely a rights issue, and you'll need to talk to your Technical Support admin.

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Friday, September 9, 2011 2:31 PM
  • Yes, I see them when I do a Dir command from the command prompt.
    Friday, September 9, 2011 2:43 PM
  • I am logging in as adminitrator.  I tried both the local administrator and the domain administrator and neither one can access the files in SQL Server.  I am able to create the folder on the new drive and copy files into it from explorer just fine, but the minute I try to open them I get access denied.  I have tried both remotely and directly on the server.  I have a feeling it may have to do with the drive and OS somehow?  The new drive was added into a RAID 0 setup that previously had 2 drives (C and D).  The new partition is spanning 2 physical drives and was given the drive F.
    Friday, September 9, 2011 3:27 PM
  • Note that when SQL Server accesses the folder and files, it uses the SQL Servewr service account, not whoever you might be logged in as. Important distinction. So, login as the Service Account, and try to access the files. If that fails, you need to work out the permission at the OS level.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Proposed as answer by Stephanie Lv Thursday, September 15, 2011 12:33 PM
    • Marked as answer by Stephanie Lv Monday, September 19, 2011 11:19 AM
    Friday, September 9, 2011 6:00 PM