What is the mssql-cli (or other tool options?) equivalent of this?
mysqldump --host servername dbname > dbname.sql
As far as I can tell, logging-in to the SQL Server with mssql-cli and running something like below will create a backup on the server, I need it locally:
BACKUP DATABASE TestBackup FILE = 'TestBackup'
TO DISK = 'C:\TestBackup_TestBackup.FIL'
GO
What's the correct way to do this? I have to do this on Linux command line.
I realize that a "dump" of a MySQL database using mysqldump and a "backup" of a SQL Server database are two entirely different methods of backup. I'm not looking specifically for a .sql file I can run; I just need a backup file, and need to be able to save the backup file to my local machine.
1 Answer 1
If you have mssql-cli already installed, you can simply run it, then type a command similar to this into the console:
BACKUP DATABASE <databaseName> TO DISK = N'/path/to/backups/databaseName.bak' WITH COMPRESSION, INIT, STATS = 10
Installing scp on your client machine enables you to use it to securely copy the backup file over to your client machine. Virtually every Linux machine out there will have scp already available. In Windows 10, you can install scp as part of the OpenSSH Client Package. Go to the Start Menu, then click Settings -> Apps -> Optional Features, and add the OpenSSH package. Once you've got OpenSSH installed, you can use a command similar to this from the Command Prompt or PowerShell to copy the file to your client machine:
scp user@server:/path/to/backup/databaseName.bak c:\local\path\databaseName.bak
If you don't have mssql-cli installed, I would recommend the more lightweight sqlcmd tool available by installing Microsoft's mssql-tools package via your package manager of choice. In RedHat, that would be yum install mssql-tools. That will by default create an executable named /opt/mssql-tools/bin/sqlcmd. Running the following will return the name of the SQL Server if you've completed that part successfully:
/opt/mssql-tools/bin/sqlcmd -S <servername> -U <login> -P <password> -Q "SELECT @@SERVERNAME;"
You'll need to replace the tokens marked with < and > with your values.
If that works, then running the following will backup a database to the path of your choice, assuming the SQL Server has permissions to write to the path in question:
/opt/mssql-tools/bin/sqlcmd -S <servername> -U <login> -P <password> -Q "BACKUP DATABASE <databaseName> TO DISK = N'/some/folder/<databaseName>.bak' WITH INIT, COMPRESSION, STATS = 10;"
The backup will be compressed, and any existing backup in that file will be overwritten. The console will show progress in 10% increments.
FYI, the backup command shown in your question includes the FILE = [...] clause which will only backup that single file. 99.999% of the time you'll want to ensure the entire database is backed up. To do that, simply remove the FILE = [...] clause. Also, you should ensure that you backup the database log via BACKUP LOG [...] if the database is running in Full Recovery model. Take a look at the BACKUP docs for more details.
Once you've successfully completed the database and log backups (if required) ensure you test those backups by restoring them somewhere else. A backup file that cannot successfully be restored is worse than not having a backup since it gives a false sense of security. Develop a recovery plan once you determine that your backup strategy is working to ensure that you can meet the desired RPO and RTO required by the business. This Docs page has actionable content around business continuity.