MS SQL Server 2008 R2 network backup

Posted on

Question :

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…

Answer :

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.

Leave a Reply

Your email address will not be published.