Your requirements require the use of the following two tools:
- sqlcmd (SQL Server Built-In CMD Tool)
- psexec (SysInternals Remote Execution Tool)
SQLCMD
First you have to have a valid SQLCMD command. This could look a bit like this:
Intentionally listed on multiple lines
sqlcmd
-S .
-U sa
-P ThisIsNotMyPassword
-d master
-Q "Backup Database StackExchange TO DISK='\\127.0.0.1\C$\Temp\StackExchange' WITH COPY_ONLY"
The different parameters are as follows:
-S : The server you are connecting to. (IP or Hostname or Localhost [.])
-U : The SQL Login you are using to connect with
-P : The password of the SQL Login
-E : Trusted connection (Log in using your current Windows Account)
[to be used instead of -U and -P]
-d : The [initial] database you are connecting to
-Q : The command (in quotes) that you wish to execute
The remote disk (from the target's perspective) you are backing up to must be accessible from the computer/server you are backing up from.
So you have the command together, now you just have to send it from your computer to the remote computer. And this is where it can become complicated.
PSEXEC
We will use SysInternals PSEXEC.exe
tool to remotely execute the SQLCMD.EXE
.
A command to execute the remote command could look like this:
Intentionally listed on multiple lines
psexec
\\192.168.1.23
-p ThisIsNotMyWindowsPassword
-u Domain\User
-accepteula
SQLCMD (from above)
The different parameters a pretty much self-explanatory.
If you don't supply a user name (-u) and password (-p), then you will signed in using your current credentials.
Packing them together
The whole command will then look like this:
No line-breaks to increase portability.
psexec \\127.0.0.1 -accepteula sqlcmd -S RemoteServer -U sa -P ThisIsNotMyPassword -d master -Q "Backup Database StackExchange TO DISK='\\127.0.0.1\C$\Temp\StackExchange' WITH COPY_ONLY"
This example works on my local machine for demonstration purposes and might require some tweaking depending on your environment.
On success:
C:\work\SITools>psexec -accepteula sqlcmd -S . -U sa -P ThisIsNotMyPassword -d master -Q "Backup Database StackExchange TO DISK='\\127.0.0.1\C$\Temp\StackExchange.bak' WITH COPY_ONLY"
PsExec v2.2 - Execute processes remotely
Copyright (C) 2001-2016 Mark Russinovich
Sysinternals - www.sysinternals.com
sqlcmd exited with error code 0.
Reference Material