3

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)

  • 1
    What information could you add to your question to allow us to provide you with a solution? What is your local (CMD) host running on? (Windows, *nix, Solaris, AIX, ...) Without additional information your question might be closed as Unclear what your asking or Too broad. – John aka hot2use Jul 23 '18 at 12:58
  • Maybe dbatools would be something to check out – scsimon Jul 25 '18 at 20:21
3

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
  • Can't use SMB, Updated question. Import Export Wizard is GUI tool, no GUI, I need command line or script – GMX Rider Jul 21 '18 at 13:10
1

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

  • He already said he couldn't use SMB shares to move the backup. – Brent Ozar Jul 23 '18 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 aka hot2use Jul 23 '18 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 '18 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 aka hot2use Jul 23 '18 at 13:53
  • If you know to do the "somehow magically" part, you should totally post that as an answer. ;-) – Brent Ozar Jul 23 '18 at 16:04
1

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.

  • 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. :( – Yeasin Ar Rahman Mar 21 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. – LowlyDBA Mar 21 at 13:40
0

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.

  • 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 '18 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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