Tuesday, March 22, 2016

How to backup a database to a network drive

As part of disaster recovery sometimes we require to take backup in network share drive. Most of the users are facing issues while doing this activity follow below steps to take back up in network drive.

Enable backups to network drive in sql server :
  • Check whether sql server running under domain account or local system/network service. if it is not running under domain account follow below steps to change service to domain account.
  • Open services.msc and click in sql services properties and change it to domain account as show below. password must be the domain user's

  • In order to take back up in remote machine your sql service must run under domain account.
  • Share the permission to the backup folder (destination machine/remote machine) and assign read/write permission to the user as shown in below image 


USE  AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = '\\Remotemechinename\\SQLServerBackupsfolder\' --Remote Machine folder path
  WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of 
AdventureWorks2012';
GO
  • As show in above syntax at to disk place replace your remote machine name and folder name which you share earlier steps.
  • Replace Remotemechinename  with machine name where you storing backups,
  • Replace SQLServerBackupsfolder with folder backup folder.
  • Run the query.