How to backup an MSSQL Database from another host using osql?

Hi ServerFault Family:

We have been performing our sql database using osql as follows:

osql -s IP-9873743\SQLEXPRESS -E -Q "BACKUP DATABASE DBNAME TO 
Disk ='e:\sysbkup\sqlbackup\DBNAME.bak' WITH NOFORMAT, 
INIT, NAME = N' DBNAME-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10"

We want to do the same from another host but when we add the -H for host option on the command we get "The login failed". With MS SQL Server Management Studio, we are able to login to the other server fine with Windows Authentication since my machine and the other host are using the same usernames with same password.

When I run the backup statement from the MGMT Studio console the backup is saved to the disk of the other host, instead of transferring the backup to the client machine. Any ideas how I will be able to backup an MSSQL database from another host different than the mssql engine itself?

From serverfault Geo

• You should be specifying the server name in the -s parameter, not -H, though this will still backup to the server the SQL service is running on.

  In my experience SQL Server is fussy about operating on network drives, but you could try specifying a UNC path (\\<server>\<share>\<backup_file>) to the place you want the backup to go to on the network. I believe that the user the SQL Server instance is running as will need write access to the destination machine, I don't think it will use your account despite logging in via Windows integrated security, so you will need to arrange that first.

  Geo : Thanks David. I tried both with -H and with -s. When I did the backup using ms mgmt studio the backup was done in the host c drive instead of the client host c drive.

  David Spillett : The path you specify is _always_ relative to the server SQL is running on, not the client that asks it to run a backup. That is why you need to use a UNC path, though for that to work you need to ensure the user the service runs as has write access to that location.

  Jason Cumberland : Don't forget that it's an upper case S, and if you're using the SQL 2005 client tools then switch to SQLCMD.exe instead. If the remote SQL Server service account is not a domain acocunt or network service then using a UNC will not be possible. osql/sqlcmd -E -S server\instance -Q "backup database db to disk = '\\otherhost\share\db.bak' with init, stats=10"
  From David Spillett