Create Sql Server Backup on Local Machine

Question

I am using SQL Server Management Studio running on my local machine.

I can log on to a remote box (database engine) and use the Studio to create a database backup which is saved to a drive on the remote box.

How do I get it to save the backup to a drive on my local machine?

Answer 1

You can't - the remote machine has no information about your local machine's setup and cannot see the drives on it. You'd have to setup a shared folder on your local machine, and make sure the remote machine has access to it (which will mean both the SQL Server Agent and SQL Server services on the remote machine will need access to it via domain accounts).

Answer 2

See this MSDN article, for the section on backing up to a network share, e.g.:

BACKUP DATABASE YourDatabase
   TO DISK = '\\SomeMachine\Backups\YourDatabase.Bak';

Backing Up to a File on a Network
Share For SQL Server to access a remote disk file, the SQL Server service account must have access to the network share. This includes having the permissions needed for backup operations to write to the network share and for restore operations to read from it. The availability of network drives and permissions depends on the context is which SQL Server service is running:

• To back up to a network drive when SQL Server is running in a domain user account, the shared drive must be mapped as a network drive in the session where SQL Server is running. If you start Sqlservr.exe from command line, SQL Server sees any network drives you have mapped in your login session.
  
• When you run Sqlservr.exe as a service, SQL Server runs in a separate session that has no relation to your login session. The session in which a service runs can have its own mapped drives, although it usually does not.
  
• You can connect with the network service account by using the computer account instead of a domain user. To enable backups from specific computers to a shared drive, grant access to the computer accounts. As long as the Sqlservr.exe process that is writing the backup has access, it is irrelevant whether the user sending the BACKUP command has access.

Answer 3

If the remote machine is on the same network as your machine, see AdaTheDev's answer.

Otherwise you'll have to RDP or FTP into the remote machine and transfer the backup manually. I recommend 7-zip'ing it by the way.