Home Forums SQL Server 2005 Administering Access Denied where doing restore from a shared folder RE: Access Denied where doing restore from a shared folder

  • From http://www.fmsinc.com/freE/NewTips/SQL/SQLtip9.asp

    While only local devices are shown in Enterprise Manager’s Backup/Restore dialogs, there is a way to create or restore a SQL Server database backup on a network file share. Creating or restoring a database backup on a network file share requires the following prerequisites:

    1) The SQL Server services, on the Server containing the instance of SQL Server, must be running under a domain-level account (e.g. A Domain Administrator account). This is accomplished by changing the "Log On" properties for the services named "MSSQLSERVER" and "SQLSERVERAGENT" on the server running SQL Server (not your local instance). When you have completed changing the log on information for these 2 services, you will need to restart the "MSSQLSERVER" service on that server. Note that this will ask if you want to restart the "SQLSERVERAGENT" as well - Answer: Yes.

    2) The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share. That means you need to have a shared location in which the logon account you specified in 1 (above) has full control rights.

    3) The file share should only be accessed via UNC name. Mapped drives may not be consistently visible to the SQL Service.

    4) You cannot specify the path by using the browse ellipses (...). You must type the fully qualified path

    MJ