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:
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)
I made a batch file and try to accomplish the same but nothing changes. It still fails for the same reason.
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:
- Make the Servers connect to the Domain and create a Domain Group named Servers.
- Add the Domain group Servers to the users allowed for the Shared Folder.
- (I don't know if it's necessary) Make the MS Sql Server service run by one standard user in all the Servers.
- 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.