Post reply

SQL Backup Help.

  • December 15, 2011 at 11:30 pm

    #1422309

    So there seems like nothing much can be done besides granting the user SysAdmin rights, is that correct?

    I think, you don't need to grant SysAdmin rights.

    You can try this:

    1. The domain account which is running the scheduled task, add that account in SQL Server 2008 logins.

    2. Add this account in db_backupoperator role for the database you want to backup.

    See if you are able to backup after that.

    Sujeet Singh

  • December 16, 2011 at 4:29 am

    #1422384

    Tava (12/15/2011)

    I don't get any errors, the script executes as a Scheduled task and then no backup gets created i have to make the user a SYS_ADMIN role inside SQL for it to work. If i used Backup_Operator it just does nothing. So I'm assuming its a security issue?

    If it was a security issue within SQL, SQL would throw a permission denied error.

    Msg 262, Level 14, State 1, Line 1

    BACKUP DATABASE permission denied in database 'Testing'.

    Don't grant sysadmin, it should be in no way necessary. Let me investigate and try and repo, but won't be today.

    Are there any failure events in the windows security event log? That log's usually fast moving, so run the job and check immediately.

    Anything relevant in the Windows application event log?

    What kind of account is SQL Server running under?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 16, 2011 at 4:56 am

    #1422393

    GilaMonster (12/16/2011)

    What kind of account is SQL Server running under?

    and make sure that account has rights to the folder you backup youre db to

  • December 16, 2011 at 5:27 am

    #1422413

    Marco V (12/16/2011)

    GilaMonster (12/16/2011)

    What kind of account is SQL Server running under?

    and make sure that account has rights to the folder you backup youre db to

    And same with the account that is running that scheduled task. Check that the it has permission to that folder.

    p.s. I tested briefly, a SQL account with no other permissions that db_backupoperator could successfully back the DB it. Further testing apparently needed

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2011 at 3:45 pm

    #1422931

    GilaMonster (12/16/2011)

    Marco V (12/16/2011)

    GilaMonster (12/16/2011)

    What kind of account is SQL Server running under?

    and make sure that account has rights to the folder you backup youre db to

    And same with the account that is running that scheduled task. Check that the it has permission to that folder.

    p.s. I tested briefly, a SQL account with no other permissions that db_backupoperator could successfully back the DB it. Further testing apparently needed

    Marco V, Gilla,

    Folder "C:\Backup" has Full Control to the "Authenticated Users" Security Group (prefer Authenticated users more than Everyone role) which includes the User running the scheduled Task.

    I'll do a bit more testing now - with db.backupoperator accounts and remove the systemadmin access...

  • December 18, 2011 at 4:00 pm

    #1422932

    Hi All,

    Backup was just Successful, I don't know why it is working now as I'm 100% sure I gave db_backupoperator access in the first place and tests failed.

    - Server Roles: I have only given the default of Public.

    - User Mappings: Mapped db_backupoperator & public to the Databases i wanted to backup (Master,msdb,our Production Database)

    I have one more site that has the same problem as the site i've just fixed so I will replicate the above and if that works then all is solved. I will see how the system updates tonight as i also ran "scheduled task" as a admin for these tests which shouldn't make a difference anyway but i have seen weirder things.

    Thanks for all your help in this, hopefully the scripts i use can be handy for other people running express edition.

    Tava

  • December 19, 2011 at 8:35 pm

    #1423368

    Tava (12/18/2011)

    Hi All,

    Backup was just Successful, I don't know why it is working now as I'm 100% sure I gave db_backupoperator access in the first place and tests failed.

    - Server Roles: I have only given the default of Public.

    - User Mappings: Mapped db_backupoperator & public to the Databases i wanted to backup (Master,msdb,our Production Database)

    I have one more site that has the same problem as the site i've just fixed so I will replicate the above and if that works then all is solved. I will see how the system updates tonight as i also ran "scheduled task" as a admin for these tests which shouldn't make a difference anyway but i have seen weirder things.

    Thanks for all your help in this, hopefully the scripts i use can be handy for other people running express edition.

    Tava

    FYI, Confirmed and all working, tested on the 2nd location changed from SysAdmin to db_backupoperator and tested successfully.

    I must have just been having a bad day since I know what I was trying to achieve and thought I did it but guess I didn't :unsure:.

    Thanks again, this thread can be closed.

  • December 20, 2011 at 1:40 am

    #1423459

    Excellent. Glad to hear.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 16 through 22 (of 22 total)

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