CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008
Pankaj Mishra
Pankaj Mishra

Reputation: 20348

How to Restore Database in Server's sql server Database?

I have 2 database test1 and test2. I got the backup from test1. And now i want to restore this backup into test2.

My test2 database in shared server. When I am doing restore it's get failed. and populate this error message.

Msg 3110, Level 14, State 1, Line 1
User does not have permission to RESTORE database 'databasename'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

i set the permission to the user of db_backupoperator. but it's still not restoring database. Please let me know that what i am doing wrong.

Thanks

Upvotes: 0

Views: 9518

Answers (3)

AgentSQL
AgentSQL

Reputation: 2930

You need dbcreator or sysadmin role to restore database. For more information on database roles you can check - MSSQL Server roles

Upvotes: 0

Sean Airey
Sean Airey

Reputation: 6372

You will need the db_creator permission in order to successfully restore a database.

From this MSDN article:

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 (for the FROM DATABASE_SNAPSHOT option, the database always exists). 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.

Upvotes: 7

gvee
gvee

Reputation: 17161

db_backupoperator can BACKUP databases only. They cannot RESTORE. http://msdn.microsoft.com/en-us/library/ms189041(v=SQL.90).aspx

Upvotes: 0

Related Questions