Command line tool / script to backup a remote SQL Server database to local disk

Question

I've been researching and googling for 10 hours and couldn't find anything, so I decided to ask here. I'm looking for a command line tool or a script (running under windows) that can backup a remote SQL Server to a local file (.sql, SQL Server backup, even CSV, all formats are acceptable).

P.S. I can't use remote SMB share for backing up DB. Also no GUI, just command line or scripts

P.P.S. The DB is in remote location and I want to backup the data in Database to any format possible in my local machine (Windows server, no SMB share connection in between available/possible)

Answer 1

There are two common ways to do this:

Easy way - with "normal" database backups - when you run the BACKUP DATABASE command, the SQL Server service on the remote box needs to write the backup to a file. The service needs to be able to write to the destination path. To do this:

• Set up a shared folder on your local machine
• Give the SQL Server service permissions to write to that share (either by specifically granting it, or by letting everyone write to it - this isn't a security lecture, obviously)
• Run the SQL Server backup, and use the UNC path to your local machine's share for the backup target, like:

If you're doing it via T-SQL, it might look like:

BACKUP DATABASE MyReallyImportantDB TO DISK = '\\MyDesktop\SharedFolderName\MyBackup.bak'

Hard way - exporting the data - if the remote SQL Server can't write to your local machine for some reason, like network firewalls or permissions, then you can export the data. A couple of common tools are:

• Exporting a BACPAC file
• SQL Server Import and Export Wizard

But be aware that those methods can be much slower and not transactionally consistent. They're best for smaller databases - say, under 10GB.

Updated requirements - you noted no GUI, no file system access, and no backups. Given those requirements, you'll be doing some manual work:

• Use an ETL tool like SSIS to export the data
• Build a custom app (C#, Java, whatever) to export the data
• Use a data sync tool like Redgate SQL Data Compare

Answer 2

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

• sqlcmd Utility (Microsoft Docs)
• PsExec v2.2 (Microsoft Docs)

Answer 3

This sounds like the perfect use of a newer tool by Microsoft, mssql-scripter. This open source, python based bad boy should be able to do everything you need. Warning: it is still technically in alpha.

• Command line - Yes
• Can export locally - Yes
• Can export schema & data to a .sql - Yes

Example Usage

# script the database schema and data piped to a file.
mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data  > ./adventureworks.sql

# execute the generated above script with sqlcmd
sqlcmd -S mytestserver -U sa -i ./adventureworks.sql

The documentation is available on GitHub.

Answer 4

I’m not completely sure what you are referring to when you mean a local file. Is this local to the sql server or local to the machine that you are running the script from?

If it is the latter, you can use PowerShell to backup the files to a local path on the sql server and then RoboCopy the files to the machine running the script.