Solved

restore database from backup in a remote location

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
SQL Log size 3 17
Compare a numeric value to a varchar w/o getting an error 4 20
Getting invalid Syntax SQL. 3 19
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

856 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