problems w/ backup/restore..permissions?

[cisscott]

I had sort of posted about this before, but the problem just keeps getting thicker and I dont think I did a good job of explaining it before..

I need 2 new databases that are basically identical to ones that already exist. In the past I have done this through a backup device I created with sp_addumpdevice. Then restored the database into the new one with the "force restore over" option checked, and it always worked perfectly. Now I'm trying to do the very same thing, but getting nothing but errors. When I go to do the restore through Ent Mgr, I get a box that says "Restoring File 1" and it just sits there hung up until I stop the operation. Alternately when I click OK to restore from device I get the message:
"cannot open backup device E:\POSDB. Device error or device off-line, see SQL Log for more details. Restore database is terminating abnormally".

The SQL Log gives this message:
"BackupDiskFile::OpenMedia: Backup device 'E:\POSDB' failed to open. Operating system error = 5(Access is denied.)."

There isn't a permissions problem with the device that I can see.. It's a public folder.

When I try it using SQL Analyzer here is my code:

USE master
GO
RESTORE FILELISTONLY
FROM POStransfer1

RESTORE DATABASE Conc_merch
FROM POStransfer1
WITH RECOVERY,
MOVE 'POS_data' TO 'S:\Program Files\Microsoft SQL Server\MSSQL\Data\Conc_merch_data.mdf',
MOVE 'POS_log' TO 'S:\Program Files\Microsoft SQL Server\MSSQL\Data\Conc_merch_log.ldf'
GO


Query analyzer returns the following errors:

Server: Msg 3013, Level 16, State 1, Line 3
RESTORE FILELIST is terminating abnormally.
Server: Msg 3201, Level 16, State 2, Line 6
Cannot open backup device 'POStransfer1'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.


Can anyone give me a direction to go to start fixing this? Thanks !

 

[SQLBill]

1. Does the SQL Server Agent service (SQLServerAgent) have access to the file on E:??

2. Is the file on E: being used by anything else at the time (new backup being done? anti-virus check?)

-SQLBill

Posting advice: FAQ481-4875

 

[cisscott]

SQLBill thanks for the reply.. Yes, it has access to E: .. . Just to be certain that there was no access problem, I tried the whole thing again using our PUBLIC folder, which has no restrictions at all. I got that same error. Also, nothing else is using E: at all.

 

[cisscott]

OK, update.. I cannot use any device for any operation .. somewhere there is a permission messed up, and Im too much of a newbie to find it. I've tried creating one on the cluster disk S:, on the local disk C:, and on the USB drive E: .. No matter where the device is physically located, i get "cannot open back up device, device error, or device off line". I have total access to everything and I am running the SQL AGENT service under the Administrator account currently. I have checked sp_helpdevice to be certain the device is actually listed in the systable. Any other advice would be appreciated.

 

[dalv]

When a backup occurs it uses the account that sql server is running under for the permissions. When you do it through Explorer etc it uses your permissions (or
the currently logged in users). Check to ensure the account that Sql Server is running under is a domain account with rights to that share. And if it's
a mapped drive change it to use UNC instead. Mapped drives are also user specific.

 

[SQLBill]

Which type of Administrator account is the SQL Server Agent service running as? Local or Domain?

Are you only trying to backup locally?

Is this the default instance or a named instance? Yes it is different and there is a different SQL Server Agent service for EACH instance.

-SQLBill

Posting advice: FAQ481-4875

 

[cisscott]

It is a named instance. I checked the log-on parameters, and the instance is logging on under STARLITE\Administrator, and so is the SQL Agent. The administrator account has Domain Admin rights. I am only trying to back up locally.