0

This question already has an answer here:

while restoring the User DB from primary to secondary replica through query in cmd prompt and got following error message

Msg 262, Level 14, State 1, CREATE DATABASE permission denied in database 'master'.

RESTORE LOG is terminating abnormally.Msg 3013, Level 16, State 1, RESTORE LOG is terminating abnormally.

provided sa permission on the user, but still it did not work. where should i need to look.

marked as duplicate by Marco, Colin 't Hart, McNets, LowlyDBA, James Anderson Jun 20 '17 at 7:32

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

Permissions

from https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

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.

  • I have sysadmin privileges,tried to restore the DB from primary server to secondary replica through SQLCMD and i got this error when i execute directly it is executing sqlcmd -E -S SQL1 -d master -Q "RESTORE DATABASE [tes] FROM DISK = N'\\sql2\share\test.bak' WITH NORECOVERY, STATS = 5" sqlcmd -E -S SQL1 -d master -Q "RESTORE LOG [tes] FROM DISK = N'\\sql2\share\tu.trn' WITH NORECOVERY, STATS = 5" – Vijaya Jun 21 '17 at 11:31
  • when i tried to execute this script by declaring scalar variable with the below query i got the error. – Vijaya Jun 21 '17 at 11:33
  • as you can see from the script i am using backup file which is network drive to restore this in secondary. – Vijaya Jun 21 '17 at 11:40
  • I added SQL Server and agent service account to shared drive, my id has been added in local administrator in secondary as well. – Vijaya Jun 21 '17 at 11:41
  • I wanted to schedule this script through Job – Vijaya Jun 21 '17 at 11:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.