Sql-server – Why isn’t the backup file created when running sqlcmd from remote machine

sqlsql server

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?

Best Answer

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
Related Topic