4

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)

0

5 Answers 5

4

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:

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
0
3

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

4
  • 1
    He already said he couldn't use SMB shares to move the backup.
    – Brent Ozar
    Jul 23, 2018 at 12:19
  • Well he's going to have to provide some form of remote share from the target (remote/SQL) server to the source (local/CMD) server. So we close the question as "Too broad" (missing information) or "Unclear what your are asking" (missing details)? I've got half the solution.
    – John K. N.
    Jul 23, 2018 at 12:55
  • No, he doesn't have to do that with the methods that I provided, scripting out the database contents over a SQL connection.
    – Brent Ozar
    Jul 23, 2018 at 13:46
  • Am I totally missing something regarding his statements about local vs. remote context and his noGUI requirements? (That's why I proposed that OP add more details to the question.) Can he remotely initiate a BACPAC file from his local machine and then somehow magically retrieve the file from the remote server? If he only has access to the database, and possibly some tools, but NOT to the remote server itself, how would he retrieve a backup (or the BACPAC package) of the database?
    – John K. N.
    Jul 23, 2018 at 13:53
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.

2
  • can you please explain what is the difference between the .bak file the sqlcmd generates and the .sql the mssql-scripter generates? As far as i understand the .bak file is more like a snapshot which provides more guarantees. But the sql file is dependent on the environment of the DB it will be restored on. Confused. :( Mar 21, 2019 at 9:53
  • @YeasinArRahman There are many differences. I suggest reading the documentation on full backups and then asking a separate question if you're still confused. Mar 21, 2019 at 13:40
0

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
  1. 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};"
-1

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.

1
  • MSSQL is in a remote server, I want to backup all the data in remote server to a local file in my windows server
    – GMX Rider
    Jul 21, 2018 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.