Tuesday, February 21, 2012

Restore ..Dont Make Me Fool!

Using restore command , I am trying to create a db on Dev server using a
back up file of a db from a Prod server. The back up file resides on Dev
server. My restore operation terminates after 2 hrs saying "you are not
server user". Keep in mind that I have very limited rights to the Dev
server (I am not a sys admin). When I talked to the admin people about
this, they said that it is happening bc I dont not have access to the
Prod server (where the back up file came from). Whan I asked them why I
do need to access Prod server when I using only the BAK file. They said,
well I am not a user in the 'sysuser' table of a db on a Prod Server. My
question is do you have to be added as a user in a db where you do a
back up in order to use it for restore? Arent they making me fool?
My understanding is if you have a back up file (BAK) file saved, you can
always restore it on any machine as long as you have rights to do
restore opeartion. Isnt it correct?You do not have to have rights (or
added in the sysuser table) to the db on machine where that back up file
came from? Please help me!!
*** Sent via Developersdex http://www.examnotes.net ***Test,
From the SQL BOL for the RESTORE statement.
Permissions
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 symin and dbcreator fixed
server roles and the owner (dbo) of the database.
RESTORE permissions are given to roles in which membership information is
always readily available to the server. Because fixed database role
membership can be checked only when the database is accessible and
undamaged, which is not always the case when RESTORE is executed, members of
the db_owner fixed database role do not have RESTORE permissions.
In addition, the user may specify passwords for a media set, a backup set,
or both. When a password is defined on a media set, it is not enough that a
user is a member of appropriate fixed server and database roles to perform a
backup. The user also must supply the media password to perform these
operations. Similarly, RESTORE is not allowed unless the correct media
password and backup set password are specified in the restore command.
Defining passwords for backup sets and media sets is an optional feature in
the BACKUP statement. The passwords will prevent unauthorized restore
operations and unauthorized appends of backup sets to media using SQL Server
2000 tools, but passwords do not prevent overwrite of media with the FORMAT
option.
Thus, although the use of passwords can help protect the contents of media
from unauthorized access using SQL Server tools, passwords do not protect
contents from being destroyed. Passwords do not fully prevent unauthorized
access to the contents of the media because the data in the backup sets is
not encrypted and could theoretically be examined by programs specifically
created for this purpose. For situations where security is crucial, it is
important to prevent access to the media by unauthorized individuals.
It is an error to specify a password if none is defined.
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:eOITkyoyFHA.3812@.TK2MSFTNGP09.phx.gbl...
> Using restore command , I am trying to create a db on Dev server using a
> back up file of a db from a Prod server. The back up file resides on Dev
> server. My restore operation terminates after 2 hrs saying "you are not
> server user". Keep in mind that I have very limited rights to the Dev
> server (I am not a sys admin). When I talked to the admin people about
> this, they said that it is happening bc I dont not have access to the
> Prod server (where the back up file came from). Whan I asked them why I
> do need to access Prod server when I using only the BAK file. They said,
> well I am not a user in the 'sysuser' table of a db on a Prod Server. My
> question is do you have to be added as a user in a db where you do a
> back up in order to use it for restore? Arent they making me fool?
> My understanding is if you have a back up file (BAK) file saved, you can
> always restore it on any machine as long as you have rights to do
> restore opeartion. Isnt it correct?You do not have to have rights (or
> added in the sysuser table) to the db on machine where that back up file
> came from? Please help me!!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||No, this information does not help. It is not a password issue of a
media set. I do have CREATE DATABASE and BACKUP/RESTORE rights but I am
not a SYSADMIN.
What I am trying to understand is "If you have a back up file (BAK) file
saved, you can always restore it on any machine (lets say machine A) as
long as you have rights to do restore opeartion to that machine (machine
A). Isnt it correct? You do not have to have rights (or added in the
sysuser table) to the db on a machine where that back up file came
from?". Is this a wrong assumption?
*** Sent via Developersdex http://www.examnotes.net ***|||Test,
You don't need an account from the originating SQL Server.
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:u7xavbpyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> No, this information does not help. It is not a password issue of a
> media set. I do have CREATE DATABASE and BACKUP/RESTORE rights but I am
> not a SYSADMIN.
> What I am trying to understand is "If you have a back up file (BAK) file
> saved, you can always restore it on any machine (lets say machine A) as
> long as you have rights to do restore opeartion to that machine (machine
> A). Isnt it correct? You do not have to have rights (or added in the
> sysuser table) to the db on a machine where that back up file came
> from?". Is this a wrong assumption?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks, Jerry.
*** Sent via Developersdex http://www.examnotes.net ***

No comments:

Post a Comment