Can MS SQL 2008 R2 backup database to a file on different network location…I’ve been unable to set network path for backup to other machine on local network via tasks-backup-destination.add location…
I believe you can. We backup to a network location although we have the location mapped to a drive.
It seems you can use a UNC as well though according to this answer http://social.msdn.microsoft.com/Forums/sqlserver/en-US/965cd9c4-f4d5-4492-a202-f09b78d89ed1/how-to-backup-a-database-to-a-network-drive?forum=sqltools.
Have a look at the answer as there’s some good considerations on here like if the sql service login has access to the network location.
I shared the UsersPublicDocuments folder on my machine, for public access from inside the network domain, and tried backing up to the UNC path:
BACKUP DATABASE [HooHah] TO DISK = N'\MY_MACHINEUsersPublicDocumentsHooHah.bak' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'HooHah Full Backup 151114', SKIP, NOREWIND, NOUNLOAD, STATS = 10
It worked fine. The backup file restored okay to my local machine server instance, which was the ultimate goal.