Rights needed to restore DB

I have an SQL 2005 box in an academic setting. Students log-in using SQL
server authentication. They can create, modify and backup their DBs'
When using the restore wizard and specifying a file location where the
backup is located they get an error.
c:\ProgramFiles\...\...\...\Backup
Cannot access the specified path or file on the server. Verify that you have
the necessary security privileges and that the path or file exists
If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate dialog box.
The back-ups are being created and can be restored by the Instructor (with
full system rights)
What am I missing here? students can write but not read there own files.
Any help is appreciated.
Todd>I have an SQL 2005 box in an academic setting. Students log-in using SQL
> server authentication. They can create, modify and backup their DBs'
> When using the restore wizard and specifying a file location where the
> backup is located they get an error.
> c:\ProgramFiles\...\...\...\Backup
> Cannot access the specified path or file on the server. Verify that you
> have
> the necessary security privileges and that the path or file exists
> If you know that the service account can access a specific file, type in
> the
> full path for the file in the File Name control in the Locate dialog box.
> The back-ups are being created and can be restored by the Instructor (with
> full system rights)
> What am I missing here? students can write but not read there own files.
BACKUP DATABASE permission is not enough for restoring. If the database
being restored does not exist, the user must have CREATE DATABASE
permissions to be able to execute RESTORE. If the database exists, RESTORE
permissions default to members of the sysadmin and dbcreator fixed server
roles and the owner (dbo) of the database.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||The users are a part of the dbcreator fixed role. Do they need spcific right
s
to the folder where the backups are stored or does a sytem account manage
that piece.
If so which account?
"Dejan Sarka" wrote:

> BACKUP DATABASE permission is not enough for restoring. If the database
> being restored does not exist, the user must have CREATE DATABASE
> permissions to be able to execute RESTORE. If the database exists, RESTORE
> permissions default to members of the sysadmin and dbcreator fixed server
> roles and the owner (dbo) of the database.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||> The users are a part of the dbcreator fixed role. Do they need spcific
> rights
> to the folder where the backups are stored or does a sytem account manage
> that piece.
> If so which account?
If you are using T-SQL Restore command from SSMS, then it is SQL Server
account. Check whether it can reach the folder. For example, you could use a
mapped drive, and the SQL Server account does not know anything about the
mapping you do under your user account. In addition, LocalSystem account, if
your server uses it, can not go beyond local computer - it can not reach
shared folders on the network. Try to login with servers account and read
the backup files.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Dejan,
We are using the backup/ restore wizard and trying to reach the default
SQL back-up folder on the local drive. The Administrator and Instructor
accounts can reach the folder and restore a file, but they have sysadmin
role. Students with dbcreator role can backup but not restore.
The SQLUser account in widows has full control rights to the folder.
"Dejan Sarka" wrote:

> If you are using T-SQL Restore command from SSMS, then it is SQL Server
> account. Check whether it can reach the folder. For example, you could use
a
> mapped drive, and the SQL Server account does not know anything about the
> mapping you do under your user account. In addition, LocalSystem account,
if
> your server uses it, can not go beyond local computer - it can not reach
> shared folders on the network. Try to login with servers account and read
> the backup files.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||> We are using the backup/ restore wizard and trying to reach the default
> SQL back-up folder on the local drive. The Administrator and Instructor
> accounts can reach the folder and restore a file, but they have sysadmin
> role. Students with dbcreator role can backup but not restore.
So, what is the exact error message?
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Here's the message:
"C:\Program Files\Microsoft SQL Server\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have
the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate Dialog box."
The File Name control is greyed out and doesn't allow students to type. Also
trying to Add a file path fails.
"Dejan Sarka" wrote:

> So, what is the exact error message?
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||Have you tried to login with the service account and access the path? I know
it does not sound logical that SQL Server would not have access to its own
path, but anway...
In addition, could you script your student login / user / permissions, in
order I can check it?
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:78358925-255D-4EB7-B5D5-8448256078E3@.microsoft.com...[vbcol=seagreen]
> Here's the message:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Backup
> Cannot access the specified path or file on the server. Verify that you
> have
> the necessary security privileges and that the path or file exists.
> If you know that the service account can access a specific file, type in
> the
> full path for the file in the File Name control in the Locate Dialog box."
> The File Name control is greyed out and doesn't allow students to type.
> Also
> trying to Add a file path fails.
>
> "Dejan Sarka" wrote:
>

RI vs Speed

Hello,

We are in the process of setting up RI (mostly foreign keys, some sp's) on our SQL Server 2000 DBs. One of the questions that has come into play is how bad the performance hit will be once the RI has been put into place. We are using VFP 7 and C# for our front ends, BTW.

I was just wondering if there was anyone out there who has seen any performance difference between an application with RI and one without. If so, how bad of a hit was it, and what type of RI did you use (FKs, SPs, triggers, etc...). If anyone has any advice on how to reduce the performance hit, that would be appreciated as well!

Thanks!

-Justinthe question should not be stated in terms of performance of RI versus performance of not having RI

the question should be performance of RI versus performance of doing the same RI actions in application code

the answer should be, let the database run the RI actions

if you are considering giving up RI, you are saying you are willing to forego having valid data, and i don't believe that's a performance question, but an integrity question

let's consider a (somewhat contrived) analogy -- what if your bank said to you "every time we transfer money from your checking account to your savings account, do you want us to make sure the money actually arrived in the savings account? or would you like to give up that assurance in order to get your card out of the ATM a few milliseconds sooner?"

rudy
http://rudy.ca/|||We recently converted all of our data to SQL Server - currently we have NO RI in place. Some of the RI we have planned to put in place is critical, and will go no matter what the impact - we just want to be prepared. Some of it is somewhat redundant and not as necessary, and the dreaded management wants to weigh the pros and cons of putting this RI into place. This why I am asking the performance question.|||i understand, and sympathize :)

having myself been a clueless pointy-haired boss in the past, i can understand where they're coming from

however, i think you still need to make the point about the purpose of RI, which is to ensure data integrity

now, granted, if it's an existing application which is being modified to use a new database, then you have an interesting situation

presumably the existing app already takes care of RI concerns itself (e.g. not inserting an order for a customer that doesn't exist) so adding RI in these circumstances seems rather pointless

in other words, there's little to be gained (in a bottom-line cost-benefit sense) from adding RI constraints to a database for an app that's already working

when framed in this context, i would suggest setting up the database without RI to start with, taking several volume test performance measures, applying RI, and running the performance tests again

i predict the RI overhead will not be noticeable, or at least no more than a 5% increase

your mileage may vary, since so much depends on the actual tables and relationships involved

rudy|||Thanks for your reply :)

The application was actually written in VFP 6/7 and was designed to use the native FoxPro tables. once some of the DBCs (there are about 30) started nearing the 2 GB limit, we had to 'tweak' (word used by mgmt, it was more like rewrite) the program to be SQL Server compliant. In the midst of all this our DBA ran screaming for the hills, so I recently got the position. The hired guns who did the data conversion neglected to convert the extensive VFP RI on the old DBs to SQL (along with other insignificant things like memos and field descriptions), and now I am stuck in cleanup mode. Since the RI has to be rebuilt from the ground up, mgmt is taking the oppurtunity to sacrifice data integrity for customer-visible speed.

I've been doing some testing on mass updates with RI hooked and then unhooked, and have noticed about a 7% hit for the essential RI. Not a big deal considering we got a 238% speed increase once we converted to SQL Server DBs (the application goes over a WAN). I haven't wriiten the SPs and triggers for the 'nonessential' portion yet, and mgmnt doesn't want me to until they are comfortable it would 'not be a waste of my' + underpaid + 'time.'

revised attach files to new box

sql2k sp3
Box crashed. Uninstalled/ reinstalled sql. I have a backup
of the master db but not for the users db's. I will
restore from the master backup. But after that, is there a
way to attach the mdf's that werent cleanly detached?
TIA, ChrisR
Try first using sp_attach_db. This requires both mdf, ndf and ldf file(s).
If that doesn't cut it, you can *try* sp_attach_single_file_db, which doesn't require ldf. With much luck, SQL
Server can create the ldf file for you.
Above two are only documented to work if you cleanly detached the databases first. It might work for you even
if you didn't. If it doesn't, open a case with MS support and see if they have any suggestions (which will
probably result in possible data-loss and/or physical and/or logical inconsistencies).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message news:ca3f01c48a14$a0052fd0$a601280a@.phx.gbl...
> sql2k sp3
> Box crashed. Uninstalled/ reinstalled sql. I have a backup
> of the master db but not for the users db's. I will
> restore from the master backup. But after that, is there a
> way to attach the mdf's that werent cleanly detached?
> TIA, ChrisR