?
Solved

restore database from backup in a remote location

Posted on 2007-11-29
5
Medium Priority
?
971 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 69

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

743 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