Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2012-09-18 : 22:23:56
|
| I am trying to setup a SQL Agent Job to do a nightly back up of on DB and then restore it to a replica DB on the same server. I have gotten the code to work if I run it from Query Analizer but once I creat a job with two steps (one to backup and one to restore) the job fails with the error "User does not have permission to RESTORE databse "ProductionReplica". I have tried setting the owner of the job to a login that is in the sysadmin server role and has the dbo_owner mapping for both DBs with no luck. I even made a login that had the db_backupopperator mapping to the two databases but still get the permission error. What do i need to do to give the user the right permission to back/restore the DB?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
sequelkid
Starting Member
3 Posts |
Posted - 2012-09-19 : 03:34:24
|
| Hi Stellvia,If you have set the job owner to sysadmin server role it should work ideally. Here is what i tried and it worked .1) Step 1:BACKUP STEP BACKUP DATABASE [Production] TO DISK = N'G:\MSSQL10.NTSTGINSTANCE01\MSSQL\Backup\Production.bak' WITH NOFORMAT, NOINIT, NAME = N'Production-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO2)Step 2 : RESTORE RESTORE DATABASE [Production_replica]FROM DISK =N'G:\MSSQL10.NTSTGINSTANCE01\MSSQL\Backup\Production.bak' WITH FILE = 1, REPLACE,MOVE N'Production' TO N'E:\MSSQL10.NTSTGINSTANCE01\MSSQL\DATA\Production_replica.mdf', MOVE N'Production_log' TO N'F:\MSSQL10.NTSTGINSTANCE01\MSSQL\Data\Production_replica.ldf', NOUNLOAD, STATS = 10GOAlso make job owner as SA.Let us know if it works.SEQUELKID |
 |
|
|
|
|
|
|
|
Need help configuring DB Backup SQL job