Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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. –  Geo Aug 4 '09 at 0:37
    
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. –  David Spillett Aug 4 '09 at 8:19
    
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" –  Jason Cumberland Mar 22 '10 at 21:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.