Create database backup with sqlcmd from remote host

by ChrisK   Last Updated September 14, 2015 14:00 PM

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?



Answers 1


-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:

-H workstation_name

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

wmz
wmz
September 14, 2015 13:17 PM

Related Questions



Samsung dex and remote desktop

Updated July 01, 2017 08:01 AM

How to ask for help using Skype

Updated December 01, 2015 15:00 PM

Where TeamViewer keeps the ID?

Updated June 22, 2017 00:01 AM