Solved

restore database from backup in a remote location

Posted on 2007-11-29
5
968 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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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