Unity 4.0 SqlNightlyBackupJOb and SqlWeeklyBackupJob failing.

[michael.taylor]

Good Day,

SQL backup jobs are failing because step 2 fails with the following log msg:

Executed as user: NT AUTHORITY\SYSTEM. The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000] (Error 4208)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

The command causing the error is

BACKUP LOG UnityDb TO UnityDbDump

Replace LOG with Database and all works OK; however, that is not really a solution.

It's partner server, located at the other end of the country is also failing the same job with a slightly different log message:

The job failed.  Unable to determine if the owner (<Domain>\UnityInstall) of job SqlNightlyBackupJob has server access (reason: Could not obtain information about Windows NT group/user '<Domain>\UnityInstall'. [SQLSTATE 42000] (Error 8198)).

I would like to know my next step.

Thank you in anticipation.

[virverma]

Try,

Open SQL Enterprise Manager.
Expand 'Microsoft SQL Servers'\SQL Server Group\\Security
and select 'Logins'.
Right-click in the Right window pane & choose 'New Login'.
Click the '...' button next to the Name field and choose the Unity Directory
Service account from the domain user listing.
Click the 'Server Roles' tab and choose the 'System Administrators'
checkbox.
Click the 'Database Access' tab and for each of the 'Unitydb', 'Reportdb'
and 'Unitydistributiondb' (if Failover is installed):
- check the box next to the database name in the upper grid.
- check the 'db_backupoperator' from the lower grid.
Click Ok.
Expand the 'Management'\'SQL Server Agent'\'Jobs'
For each of the 'SqlNightlyBackupJob', 'SqlWeeklyBackupJob':
- Right-click on the job and choose 'Properties'
- From the 'Owner' drop-down, choose the user added in the steps above.
- Click 'Ok'
To ensure the job completes successfully, right-click on the job and choose
'Start Job' - running the Nightly backup job first.
To see the outcome of the job, right-click on the job and choose 'View Job
History' - check the "Show step details" checkbox.

this should help!

[michael.taylor]

The UnityInstall Domain User was already a member of BUILTIN\Administrators; however, added user but popup returned a Error 15405: Cannot use the reserved user or role name 'dbo'.  I could not drop dbo from the database because UnityInstall is the db_owner?.  Now I cannot delete the Login without actioning the following popup: Removing this login will remove all associated database users (if any). Are you sure you want to remove this login?.  I responded with No.  Backup jobs continue to fail with the same symptoms.  Just checked owner of Unitydb and Reortdb; both return UnityAdmin.

Ran the job as sa: The differential step 1. DifferentialUNityDbBackup worked fine.  Step 2 BackupUnityDbLog failed with the same message as my other box: Executed as user: "NT AUTHORITY\SYSTEM. The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000] (Error 4208)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed".

Further assistance would be much appreciated