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

Let's start from afar, since, according to the condition of the problem, SNB cannot be used :-)

It's necessary to start with the fact that backups are divided into two types, physical and logical.

Physical

This is copying data from database files to a backup file without significant transformations. For SQL Server, this is the BACKUP DATABASE command - the DBMS performs a low-level paging copy from the database file to the backup file.

For a "Physical backup", a file is created and filled by the DBMS itself. A backup can be performed to a storage to which a DBMS has file access.

Physical backups, as a rule, are created much faster and restored much faster. In addition, SQL Server physical backups support incremental backups (Transaction Log, Differential).

Logical

This is a copy of the logical representation of the database data (what we see in SQL Server Management Studio). During a logical backup, backuptool essentially performs a lot of SELECT queries on system and non-system tables to understand what the database consists of, and then writes it to the backup file. For SQL Server, a logical backup is usually created in the form of a .bacpac file, or as a T-SQL script containing instructions for creating a database.

For a logical backup, a file is created and populated by backuptool. Where you run it, there a backup will be created

Logical backups, on the contrary, take a long time to create, load the database very heavily all this time - and take a very long time to restore. In addition, to ensure the transactional consistency of a logical backup in SQL Server, you first need to take a snapshot of the database, and make a backup of the snapshot, and not the database itself.

---

In most situations, it's recommended to perform a physical backup

BACKUP DATABASE [AdventureWorks] TO DISK = ...

A backup file transfer to another server is a separate issue. If it is not possible to do this via SMB, then you can transfer the file via FTP - or Google Drive. By the way, there is a command line utility SqlBak-CLI that can do both backup and restore the database on FTP.

---

For a small database, a logical backup can be done remotely, the main thing is that the user under which the connection to the database is set has enough read privileges.

Microsoft provides two command line tools to create a logical backup:

1. MSSQL-Scripter - creates .sql which will create the tables and fill them with data as in the original database. Usage example:

# set environment variable MSSQL_SCRIPTER_PASSWORD so no password input is required.
mssql-scripter -S 192.168.3.4  -d AdventureWorks -U sa -f ./adventureworks.sql

2. sqlpackage.exe - exports the database to a .bacpac file. In fact, this file is an archive in which a description of the tables and what they contain are stored in XML format.

SqlPackage.exe /Action:Export /TargetFile:"C:\AdventureWorkss.bacpac" \ /SourceConnectionString:"Server=tcp:192.168.3.4,1433;Initial Catalog=AdventureWorks;User ID=sqladmin;Password={your_password};"

Answer 5

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.