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:
>

Rid errors

I have a database that is showing quite a few rid errors in the event
logs...I know this is not a good thing, however I was able to restore a
good backup which at this point that seems to be clean.
My question is: Can anyone explain, or point me to a website that
explains in detail what exactly rid errors are. I know they can be
caused by a number of things such as a pc crashing or a bad hard drive.
ThanksYes, you can fix this problem. It is not seroius problem but it could be if you left it as is.
This error may occur, as you say 'pc crashing or a bad hard drive' and it means that Row identifier is not pointing to the wright row any more. Usualy you could solve this problem by issuing DBCC CHECKDB, and if not try to rebuild indexes on specific table with DBCC DBREINDEX. Certainly you should check hardware : disk, power.... bacause this error IS "hardware error"
Hope it Helpssql

richie robles

How do you copy a 'production' database from the same group to a development database. I've been told that a restore is the hot ticket, but I'm afraid of overriding data.

Quote:

Originally Posted by Richie Robles

How do you copy a 'production' database from the same group to a development database. I've been told that a restore is the hot ticket, but I'm afraid of overriding data.

Hi
create a backup of your development database and then do the restore of the production db. Keep the .bak safe somewhere :)

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, ChrisRTry 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

Reverse data processed...

Hi All,
I have a very big database (110GB+). It takes a long time to restore or
even just copy...
Now, I have a process that will alter a lot of data in this DB over a lot of
tables. And this process has a bug in it... In order to debug this
process, I would need to have to keep restoring this DB into its initial
state. The process takes about 45 min to 1 hour.
Would someone be kind and tell me:
1) Is there a way to reverse the changes this "Process" made quickly? i.e.
Quick restore. Obviously, I have a full backup.
2) A good old restore takes about 4 hours from a local drive
3) A good old file copy takes about 2 hours.
4) What I am really looking for, is a way to undo the DB to certain point
in time. Like a restore point...
Anyway idea?
Thanks,
AlexHello Alex,
Depending on your process, you may be able to successfully accomplish what
you need by building some error handling into your process that will
ROLLBACK TRANSACTION when an error is encountered. Note that you can not
ROLLBACK once a transaction has been committed, so this would have to
happen before a COMMIT TRAN statement. It may also be a good idea to
create a log entry in the NT Event Logs to provide more information about
the details of the error. You can use RAISERROR to call messages stored in
the sysmessages table of your database, which can be configured using
sp_addmessage. For example:
BEGIN TRAN T1
..statements...
IF @.@.ERROR <> 0
BEGIN
RAISERROR ('Error Message Text', 16, 1) WITH LOG
ROLLBACK TRANSACTION
END
..statements...
COMMIT TRAN
For more information about using RAISERROR to provide specific information
about the error that you're receiving, please refer to Books Online and
search for RAISERROR. This will fully describe the parameters that are
available with RAISERROR.
Another way to manage your issue, depending on your testing requirements,
might be to debug your process against a smaller data set. By pulling a
subset of data from the VLDB you should be able to test you process, break
your data, and restore your data much faster. You can use DTS to transfer
the data over to a new database and then run your process against that.
If working against a smaller dataset is not an option for you, another
method would be to move the affected tables to a different filegroup. Once
you have run your process you could restore just that one filegroup, which
should be smaller (and faster) than a full restore.
Restore points and checkpoints are used by MS SQL Server only during the
restore process, so these will not be helpful for you as a "rollback to X
point" option. There may be third party solutions available on the market
that provide this level of functionality.
Please let me know if you have any other concerns, or need anything else.
Hope this helps!
Sincerely,
Dana Brash
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Smartikat" <Smartikat@.online.nospam>
>From: "Smartikat" <Smartikat@.online.nospam>
>Subject: Reverse data processed...
>Date: Tue, 25 Jan 2005 16:40:20 -0800
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#PSre#zAFHA.4004@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: 63.171.237.69
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.programming:499552
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Hi All,
>I have a very big database (110GB+). It takes a long time to restore or
>even just copy...
>Now, I have a process that will alter a lot of data in this DB over a lot
of
>tables. And this process has a bug in it... In order to debug this
>process, I would need to have to keep restoring this DB into its initial
>state. The process takes about 45 min to 1 hour.
>Would someone be kind and tell me:
>1) Is there a way to reverse the changes this "Process" made quickly?
i.e.
>Quick restore. Obviously, I have a full backup.
>2) A good old restore takes about 4 hours from a local drive
>3) A good old file copy takes about 2 hours.
>4) What I am really looking for, is a way to undo the DB to certain point
>in time. Like a restore point...
>Anyway idea?
>Thanks,
>Alex
>
>|||If you have the disk space, detach the database using sp_detach_db, make
copies of the files, then just keep making a fresh copy of the file every
time and then use sp_attach_db to reattach it. Should be much faster.
You might also just consider wrapping the whole thing in a transaction and
step through the process a few statements at a time and rollback when you
find a bug. If it is a single statement taking a bulk of the time, post it
if you can and someone might be able to help you find your problem.
This might sound preachy, but you might want to execute the code "manually"
by walking through and tracing through all of the joins mentally and on a
white board. It can really make the difference if you can wrap your head
around it, you may even find errors that don't crop up in simple testing.
Happy hunting!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smartikat" <Smartikat@.online.nospam> wrote in message
news:%23PSre%23zAFHA.4004@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a very big database (110GB+). It takes a long time to restore or
> even just copy...
> Now, I have a process that will alter a lot of data in this DB over a lot
> of tables. And this process has a bug in it... In order to debug this
> process, I would need to have to keep restoring this DB into its initial
> state. The process takes about 45 min to 1 hour.
> Would someone be kind and tell me:
> 1) Is there a way to reverse the changes this "Process" made quickly?
> i.e. Quick restore. Obviously, I have a full backup.
> 2) A good old restore takes about 4 hours from a local drive
> 3) A good old file copy takes about 2 hours.
> 4) What I am really looking for, is a way to undo the DB to certain point
> in time. Like a restore point...
> Anyway idea?
> Thanks,
> Alex
>

Revcovery

Hi all,
I am trying to restore a database and get a 3234 error saying that the
logical file is not part of the database. I did a detach and reattach to the
database then a backup. Now I want to do a restore. Can anyone help. It does
say that I should use Restore Filelistonly. What does that mean and how do I
use it. Any help is appreciated.
Thanks
Tom
You can determine the logical file names from the backup file using RESTORE
FILELISTONLY:
RESTORE FILELISTONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:Mkl5d.63161$6h7.49180@.twister.rdc-kc.rr.com...
> Hi all,
> I am trying to restore a database and get a 3234 error saying that the
> logical file is not part of the database. I did a detach and reattach to
> the
> database then a backup. Now I want to do a restore. Can anyone help. It
> does
> say that I should use Restore Filelistonly. What does that mean and how do
> I
> use it. Any help is appreciated.
> Thanks
> Tom
>
>

Revcovery

Hi all,
I am trying to restore a database and get a 3234 error saying that the
logical file is not part of the database. I did a detach and reattach to the
database then a backup. Now I want to do a restore. Can anyone help. It does
say that I should use Restore Filelistonly. What does that mean and how do I
use it. Any help is appreciated.
Thanks
TomYou can determine the logical file names from the backup file using RESTORE
FILELISTONLY:
RESTORE FILELISTONLY
FROM DISK='C:\Backups\MyDatabase.bak'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:Mkl5d.63161$6h7.49180@.twister.rdc-kc.rr.com...
> Hi all,
> I am trying to restore a database and get a 3234 error saying that the
> logical file is not part of the database. I did a detach and reattach to
> the
> database then a backup. Now I want to do a restore. Can anyone help. It
> does
> say that I should use Restore Filelistonly. What does that mean and how do
> I
> use it. Any help is appreciated.
> Thanks
> Tom
>
>