Post reply

Error during Restore: Msg 15247, Level 16, State 1, Procedure sys.sp_MSrepl_chec

  • April 18, 2023 at 8:16 pm

    #4175858

    My Backup/Restore job is failing - only when the DB is involved in REPL.  Looking to see if anyone has a resolution:

    So this works fine:

    • Create transactional PUB1 (all tables, views, procs) on Server1 DB1
    • Create SUB1 on Server 2 (populate DB1 on Server2) pulling from PUB1 on Server1

    We now have req'mnt to BACKUP the Subscribing DB1 on Server2 and Restore it to DB1_STAGE on Server2 - which is failing:

    • On Server2, Backup DB1 to local T: drive
    • Restore DB1_Stage on Server2 from the Backup taken in previous step

      (NOTE: this Backup/Restore job works fine when the SUB is not created)

    Error encountered here:  (only when SUB1 is in place)

    Msg 15247, Level 16, State 1, Procedure sys.sp_MSrepl_check_job_access, Line 112 [Batch Start Line 17]  User does not have permission to perform this action.  The replication agent job 'My Server1 Transactional job' was not removed because it has a non-standard name; manually remove the job when it is no longer in use.

    All Permissions are fine.. When I drop Server2 SUB1, the Backup/Restore job works fine.  My system tables are fine (no remnants of bad REPL jobs)

    Any thoughts on this error and how to work thru it?  thx in advance

    • This topic was modified 1 year, 7 months ago by  Express12.

    BT

  • April 19, 2023 at 6:01 pm

    #4176403

    Probably a dumb suggestion, but do you get the error if you run the restore as sa? I KNOW you said permissions are good, but when the system gives me a permission related error, I generally attempt to repeat it with a more powerful account (such as sa).

    I personally find it strange that a RESTORE would be trying to modify a job... jobs are not tied to the restore (unless you restore system databases), so I don't understand why you would get that error on a restore.

    Are you doing the backup/restore using the native backup/restore AND using TSQL for it OR are you using a 3rd party tool and/or a wizard to do the restore? If you are using a wizard and native backup/restore, I encourage you to use TSQL instead. If it is a 3rd party tool, I would check if there is something on the tool that needs to be checked/unchecked to make this work.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 1 (of 1 total)

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