I want to execute a database backup through SQLCMD from a remote host.
My command I use looks like this:
sqlcmd -H IP.OF.Machine -S .\SQLDOTNET -U User -P Password -Q "Backup Database [database_name] TO DISK= 'C:\backups\backup.bak'"
When I execute this I'll get an error:
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. . Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired. Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
I double checked that my server accepts remote control (Server => Server Properties => Connections => remote connection is checked).
When I execute the command on the database server the backup gets created properly.
Is there a property or configuration of the server that I'm missing here?
-H sets your workstation name (if you want to set it). From https://msdn.microsoft.com/en-us/library/ms162773%28v=sql.110%29.aspx:
A workstation name. This option sets the sqlcmd scripting variable SQLCMDWORKSTATION. The workstation name is listed in the hostname column of the sys.processes catalog view and can be returned using the stored procedure sp_who. If this option is not specified, the default is the current computer name. This name can be used to identify different sqlcmd sessions.
You need to specify server name in
-S, together with instance. A dot means "local", so you're trying to connect with instance named
SQLDOTNET running on your local machine. You need to replace the dot with the name of your remote SQL server (or it's address if you prefer).
I would also advise to connect to the server and run your commands interactively first (as a test). See here for usage: https://msdn.microsoft.com/en-us/library/ms180944%28v=sql.110%29.aspx