What access is required to restore from a remote bak file?

[RRinTetons]

I am attempting to restore a SQL Server 2000 database onto a new SQL Server 2005 instance for testing. OldServer and NewServer are both in the same domain, I'm an admin in the domain. My domain account can run backup and restore scripts locally both servers.

From SSMS on NewServer I attempt to run:

RESTORE DATABASE MYDataBase
FROM DISK = '\\OldServer\OldDB.BAK'
	WITH REPLACE

I get the message:
---------------------------------------
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\OldServer\OldDB.BAK'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
---------------------------------------

I've also tried it after deleting the empty MyDataBase on NewServer and removing the WITH REPLACE option - same result.

I've given the login account that's running the restore on the new server every permission I can on OldServer, but still the same error. I can map the volume from OldServer on NewServer and copy files from it just fine. SMSS can connect to the database server on OldServer just fine, although it cannot run the Copy Databse Wizard. I'm assuming that's something to do with permission for DTS as the error is just 'the job failed' with no further detail.

Does the query from SMSS on NewServer run under the login account with which I logged into NewServer?

If not, what account does it run under?

If so, what permissions might be addtionally required to get it to run?

I'd love to just copy the bak file locally and do the restore from the wizard, but it's too big and the wizard doesn't like restoring from a remote volume.

I'm a bit puzzled...




-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

The restore runs under the account which the SQL Server is running. So you'll need to configure the SQL Server to run under a domain account with read access to that network share.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[RRinTetons]

Got it. One lonely directory on a SAN volume connected to the remote server didn't have access for the domain account that all SQL stuff is supposed to be running under.

It was easier once I knew what I was looking for .

Thanks. Restore is running. I found some stuff about transferring logins and passwords between the old SQL 2000 server and the new SQL 2005 server at:

http://support.microsoft.com/kb/246133/

Any comments on that chore?


-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

Yep, that's the correct process.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog