We help IT Professionals succeed at work.

backup database to remote server

Hi,

Is it possible to backup database on remote server; exmpale: server1 has database, I want to have its backup should be placed on server2 c drive test folder?

Thank you
Comment
Watch Question

Commented:
mk39,
If you are wanting to have the backup from servera go to serverb, I would create a hidden share on server b, map a drive on servera to that share and then target my database backups to it.  

Author

Commented:
How to create a hidden share, please tell me more.

Thank you
Commented:
Certainly,
If you create a share and then append a $ symol to the end of the share's name this will make it hidden. To access such a share you need to know the name to access it.  ex: share name c:\share$  you would then need to map a drive and include the dollar symbol to access path = \\server\share$

Author

Commented:
Do u mean path should like
path = \\serverb\share$

Is it correct?

Thank you
Hi,

Yes you can do it but, make sure the remote back folder is shared with proper rights to that folder.

Otherwise backup to local drive and copy it to remote location.

Commented:
mk39
Correct on the path the dollar sign would set the share as hidden.

Author

Commented:
ID: 32992951

Diidnt get!! please can u explain what did u mean?
      

Author

Commented:
I am using Microsoft.SqlServer.Management.Smo namespace

Which property specify backup to overwrite previous not append it?

Thank you
Commented:
Also remember that the Backup command is ALWAYS execute as the Service Account user that starts the SQL Service.  So if you have SYSTEM as the login account in the Service you will have to switch it out for a Domain user or a windows user.  You can do it still with SYSTEM, but you will have to use the MACHINENAME$ as the account to give permissions to the share.

So if you create a share called backup$ on the machine \\backupserver, then on BACKUPSERVER server you will create a share and call it backup$ that points to c:\backups.

Then you will look at the SQL Server machine.

Say it is named SQLSERVER and you go into Administrative Tools | Services and see the SQL Server service starts with DOMAIN\username.

You will need to give DOMAIN\username permissions to the Share you just created on BACKUPSERVER and then you will need to go to the directory of the share c:\backups and give modify permissions to DOMAIN\username.

Then you will backup to \\BACKUPSERVER\backup$\name.bak

If you see that the SQL Server service starts as SYSTEM then you will need to give permissions to the backup$ share to SQLSERVER$.  It is not recommended to do this because then ANY service that starts with SYSTEM will have access to that folder.  It is better to have a named user start SQL Server service and isolate access to the backup folder to that user.

Hope that made sense.
Steve WalesSenior Database Administrator

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.