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.

I tried running the sqlcmd from a remote host to do a simple backup of a sql 2008 database. The command goes something like this:

sqlcmd -s xxx.xxx.xxx.xx -U username -P some_password -Q "Backup database [db] to \  
disk = 'c:\test_backup.bak' with format"

I get a succesfull message but the file isn't created.

When I run this on the sql manager on the same machine, it works. I thought it was permission problems, but I'm using the same username in both cases.

Any thoughts?

share|improve this question
    
What does the error log say? –  user3914 Dec 27 '10 at 18:54
    
Did you check the remote SQL server's C: drive to see if the file was created there? –  KJ-SRS Dec 27 '10 at 19:18
    
Checked the remote and no, it wasn't created. Checked locally just in case, but still nothing.... for the error logs...none, the process was successful. –  Ed Gl Dec 27 '10 at 19:50

3 Answers 3

Follow this guide .

You need to create a SQL script that backup the database specified in it on the desired location, then you can call that sql script from SQLCMD .

For example if you call the backup script backup.sql :

sqlcmd -S .\SQLEXPRESS -i backup.sql -o output.txt

Example of backup.sql :

DECLARE @Path NVARCHAR(1000), @FileName NVARCHAR(255), @FullPath NVARCHAR(1255)
SET @Path = '\\db1\E$\SQLServer\2008\Backups\'
SET @FileName = 'Express_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + '.bak'
SET @FullPath = @Path + @FileName

BACKUP DATABASE Express
TO DISK = @FullPath
WITH INIT
share|improve this answer

If the server is running Windows 2008 or higher it might have problems writing the file to the root of the drive. I've seen this before. Create a folder on the C:\ and backup the file to the folder.

share|improve this answer

Op, are you expecting the backup file to appear on the remote host where you're executing sqlcmd?

If so, you need to specify a UNC path instead of a local (to the host executing sqlcmd) path. The SQL Server does not recognize the remote-host's local path....

share|improve this answer

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.