1

I'm facing with a situation where backups are being transferred manually to a Shared Folder and my goal is to optimize all of this.

What I have to deal with is:

  • 8 Windows 2008 R2 Servers
  • 2 Windows 2003 Servers
  • All databases are MS SQL Server 2008
  • 1 Windows 2008 R2 Server explicitly for backup
  • All Servers are on local not on Domain

What I've tried so far:

  1. I had a query that copies the backup to a Network Shared Folder hosted by the Backup Server but it fails to run since the SQL Server service is executed by a different user (sqlservice), I login to MS SQL Server with (sql_admin) and my Windows login is (DBAJhonDoe)

  2. I made a batch file and try to accomplish the same but nothing changes. It still fails for the same reason.

  3. I made a login for Sql Server with my Windows user with sys_admin privileges but the same thing happens.

Considerations so far:

  • The Backup Server has the same user (DBAJhonDoe) with the same password
  • All the Servers with Sql Server has the (DBAJhonDoe) user as well

Since I have to make this works, I have to make a plan and present it to the IT Department, so I need to be specific for what I need.

What I believe is I need to change a few things:

  1. Make the Servers connect to the Domain and create a Domain Group named Servers.
  2. Add the Domain group Servers to the users allowed for the Shared Folder.
  3. (I don't know if it's necessary) Make the MS Sql Server service run by one standard user in all the Servers.
  4. Restart the Server and place the script in a Job.

Is there anything I am missing? Is there anything I can be doing better? Is there something I can make to be even more reliable since my Goal is to accomplish a self driven backup server that stores backup from all other Servers.

Improve this question
2
  • 3
    A simple powershell script will help you to move your backups to a network share. Do not use tsql to do filesystem tasks like copying / moving files, etc Modify the script that I linked as per your needs. Also, I would suggest you to use Ola's backup solution which is more flexible and takes care of backing up to n/w shares. Make sure to use backup compression. – Kin Shah Aug 11 '16 at 18:58
  • I agree with Kin. I highly reccomend Ola's scripts. For this to work you need to grant permission to the acct that SQL Server is running under to write to the share. This can be done as a local machine acct but it's easier to use a domain acct. – Sir Swears-a-lot Aug 15 '16 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.