permission to access .BAK files for test restore

Question

Post reply

permission to access .BAK files for test restore

AaP-385828

SSC Enthusiast

Points: 136
More actions
January 16, 2007 at 1:01 pm

#193852

currently our SQL Servers are 'logged off' and I would like to be able to access the .BAK files in order to perform test restores.
Is it possible to do this by connecting to each server through the Computer Management console ?
any advice on accomplishing this goal?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

If you want to restore using EM, you'll need to have the backup-file(s) at the server itself.

If your bak-files are on a remote location, you can use e.g. QA and tsql

restore database 'xyz' from file='\\yourbakfileserver\thebakfileshare\thebakupfile.bak'

you will have to point to new sql-filelocations (mdf,ndf,ldf) if needed.

Books online has more on this.

Be carefull you don't overwrite the prod-db unintended, so prepare your action !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

AaP-385828 SSC Enthusiast Points: 136 More actions · Answer 2

thanks alzdba

What group rights would I need on each SQL Server machine in order to restore remotely from the .bak location ?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

- your sqlserver-serviceaccount or your sqlagent's (if you're using a job) need read-auth for the .bak-file

- you as sql-user ... you need to be at least db-creator or database-owner of the db you're restoring.

BOL says :

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 sysadmin 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.