?
Solved

restore database from backup in a remote location

Posted on 2007-11-29
5
Medium Priority
?
979 Views
Last Modified: 2008-02-19
I backup up a SQL 2005 database to a remote location and I am trying to restoring it to another SQL 2005 Server, but I am getting the following message:

Msg 3201, Level 16, State 2, Line 2
Cannot open backup device '\\remotelocation\DatabaseName.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 2
RESTORE FILELIST is terminating abnormally.

I checked the folder rights and I have full rights to that folder.  I'm logged in as sa on the box that I am doing the restore.  Any ideas?


This is the script that I am running for the restore:

RESTORE DATABASE  databaseName
FROM DISK ='\\remotelocation\databasename.bak' with replace,
MOVE 'databasename' to 'E:\SQLDATA\databasename_Data.mdf',
MOVE 'databasename_log' to 'F:\SQLlogs\databasename_log.ldf'
0
Comment
Question by:yanci1179
  • 3
5 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 20374508
Books Online has these tips (emphasis added):

If using either a network server with a UNC name or a redirected drive letter, specify a device type of disk.

The account **under which you are running SQL Server** must have READ access to the remote computer or network server in order to perform a RESTORE operation.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20374578
If the remote location is a share you will also need permissions to the share.
0
 

Author Comment

by:yanci1179
ID: 20374594
i have full permissions to the share
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20374738
Do you get the same error if you run:

master.dbo.xp_cmdshell 'dir \\remotelocation'
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20374752
Don't forget to make sure and check what ScottPletcher pointed out...the account that starts your SQL Server needs permission to the remote location too.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question