Is it possible to create a local backup file of a remote Microsoft SQL Server database, from a linux command line?
I know I can use SQLCMD from a Windows command line, but is the same possible from a linux box?
-
You could try it with sqlcmd]. There is no backup-function. So you will have to script it yourself. - github.com/bmc/sqlcmd/blob/master/doc/users_guide.rst– Bart De VosMay 26, 2011 at 12:12
-
Do you mean the backup file to be local to the SQL server or the Linux server?– Paul KroonMay 26, 2011 at 16:43
-
ideally to linux server, but I know that SQL Server uses it's local storage for backups - as @bitplayer points out.– AndrewMay 26, 2011 at 20:07
Add a comment
|
3 Answers
When you issue a backup command with sqlcmd from a windows command line, you are telling the server to backup a database. sqlcmd just relays the command; it doesn't really know what you have asked SQL Server to do. I would be surprised if any other tool worked in any other way.
SQL Server expects to write (and read, if required) backups to either local storage (something with a drive letter, like c:\temp\mybackupdir) or a UNC path (something that looks like \hal9000\mybackupshare).
A local path could be an iSCSI device as long as Windows can deceive SQL Server into thinking it is a "real" local drive. There is no built-in support in SQL Server for nfs, ftp, scp or anything like that. I know and/or presume that there is software that can make an nfs or ftp "share" look like a local path. I'nm not so sure about scp. There is no raw data output that you could netcat into a file, either.
Another common problem is that, on windows, drive mappings are per-user. This means that you could log into that windows machine, permanently map the R drive (or whatever drive) with stored credentials and the SQL Server will not see the mapping because the SQL Server service is probably running under a different account.
A UNC path could be a share on a Samba server, which could be on the same host that you are running that *nix command tool from.
You'll have to watch out for the issues that bitplayer mentioned, but you can take care of running SQL remotely in Linux with FreeTDS. There is a Perl module to run it as well: http://www.perlmonks.org/?node_id=392385.
You can use that to remotely run the SQL needed to perform your backups. You could then possibly mount a Windows share in Linux to pull the backup files, unless you have another way to get the backup files to where you want.
answered May 26, 2011 at 22:12
Search for an ODBC driver for SQL server to run on your OS. Using this ODBC, you can connect (provided you have the permissions) and send backup commands to the SQL sever. If you can map an UNC path to your local drive, you can backup to this location.
