We ran into a problem while trying to backup our development and user testing databases to a data storage share on another server in the network. We checked and double checked location information, the backup plan, user roles and permissions, and so forth and couldn’t find the solution. The answer has to do with shared folders.
Because the location for the backup files in on a remote server, the following must be completed for the maintenance plans to run correctly:
1. The File storage folder on the remote servers must be a shared folder.
2. To create the file storage folder, you must log on remotely to storage share and create the shared folder there. You cannot create the shared folder from another box.
3. You must add both a logon for backup job and a logon for the hardware box the data is coming from to the shared folder permissions.
4. Obtain the log on for the hardware box the data is coming from by remote logging onto the box and checking the Administrative Tools>Services > SQL Server Agent>Properties>Log On for the instance of SQL that is being backed up. CTRL+C this to the clipboard.
5. Remotely log on to the server receiving the backup and navigate to the shared folder created in step 2. Open the Properties on the shared folder and go to Sharing tab>Permissions button. Add the log on for the box obtained in step 4 and the log on used by the Maintenance Plan job (the sql admin account). Save and log off the storage share.
Run a test backup and check the shared folder to see if the BAK, TRN or other file is there.
*I take absolutely no credit for finding this solution. Thanks to the team.
No comments:
Post a Comment