Announcement

Collapse
No announcement yet.

sql maint plan to unc path with diff user account

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • sql maint plan to unc path with diff user account

    Hi there,
    I am trying to schedule backups for a bunch of databases to another server which i can remote to from our sql server using serveraname\userid.
    sql service runs with account serverbname\sqlservice.
    The sql server is in a standalone / workgroup config.

    I've set the backup to be on local disk for now.
    I've tried creating a mapped drive and using either this or the unc path, trying to add the serverbname\sqlservice to back share on serveraname, trying to create a robocopy script to copy the .bak files from serveraname to serverbname but none of these have worked


    How do we go about doing this?


    Also a nice to have feature would be some staged cleanup task - im guessing i'd have to use a separate script / command to do this - along the lines of keep daily backups for a week or two, then weekly backups for 6 weeks, then monthly backups for a year etc.
    Currently I'm manually creating occasional copies of database backups for long term storage

  • #2
    Re: sql maint plan to unc path with diff user account

    If the backup job is running under serverbname\sqlservice, you will need to give write permissions to serverbname\sqlservice on both the servera share and the servera folder in the local filesystem.

    Share permissions allow remote connection but do not override filesystem permissions, so you need to add those, too.

    In an Active Directory scenario you would need SQL running as a domain user instead of a local user to allow the jobs to access a network share.

    Comment


    • #3
      Re: sql maint plan to unc path with diff user account

      I tried adding serverbname\sqlservice to the share and folder security on serveraname, however as it's a workgroup config and local account without AD this doesn't work..
      I can create a mapped drive if this is might be any help with a script to perform a move and 'archiving' of files from local SQL backup to the remote computer..?

      Comment


      • #4
        Re: sql maint plan to unc path with diff user account

        My memory failed me. I recalled being able to use a foreign security db, but that was probably way back in LANMAN days with mixed Win95/WinNT machines.

        Okay, I took two lab machines off the domain and into a workgroup, and here is what I got to work. It isn't pretty, but it works.

        I have server SQL1 running SQL as local user account SQL1\SQLDE . On server TDUM I created C:\Share, shared as "Share". I created account TDUM\SQLDE and assigned the same password I have on SQL1\SQLDE, then gave modify permissions to TDUM\SQLDE on share TDUM\Share and on C:\Share folder on TDUM. Then, as sa, ran BACKUP DATABASE AdventureWorks to DISK = '\\TDUM\Share\AdventureWorks.bak' . It worked.

        The ugly bit is that Windows will try to use your local login name and password to log into a foreign share, so if the other machine has an identical account name and identical password then you get access.

        So, translating to your situation, create account sqlservice on servera and assign the same password that serverb\sqlservice is using. Then assign modify share and folder permissions to servera\sqlservice.

        (In case you're wondering, at one time TDUM had a companion server TDEE.)

        Comment


        • #5
          Re: sql maint plan to unc path with diff user account

          Thanks Brother..!
          Yeap that works great as a query but doesn't work when set in maintenance plan (from the GUI at least)..
          Is there a way to incorporate this into maintenance plan so I can view success history etc?

          Also is there a way to tidy up the backups..

          We have one DB backup that is generally >1GB in size and includes all historical DB content for last week so we only need to retain a weekly backup plus the latest one.
          Also would be nice to then retain monthly backups after about 6 weeks if poss...

          Comment


          • #6
            Re: sql maint plan to unc path with diff user account

            OK this has been working fine backing up SQL running as serverbname\sqlservice of one standalone server to another.
            Now I need to backup SQL which is running on a standalone AD DC to the other workgroup server.
            How do I do this as the DC doesn't have local user accounts..?

            SQL is currently running as local system so do i set it to run as a AD user, then create a local user on the workgroup computer with same username and password and write access to backup for the maintenance plan??

            Comment


            • #7
              Re: sql maint plan to unc path with diff user account

              bump..
              Anybody able to comment on the above?

              Comment


              • #8
                Re: sql maint plan to unc path with diff user account

                Use a domain account?
                Tom Jones
                MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                PhD, MSc, FIAP, MIITT
                IT Trainer / Consultant
                Ossian Ltd
                Scotland

                ** Remember to give credit where credit is due and leave reputation points where appropriate **

                Comment

                Working...
                X