backup MSSQL database to a client's local disk over network

Question

I'm trying to backup a MSSQL server from linux. I'm able to connect to the server using tsql (from the freetds project), and I'm even able to backup the database on the server using the backup database command, but I haven't been able to download this backup onto the machine I'm using to connect to the MSSQL server. If this was a mySQL server, I could download a backup of the database using mysqldump. Is there something similar I can do for an MSSQL server in linux?

Answer 1

This question on SO suggests a possible approach: Dump your data from MSSQL into MySQL. Then you can use the MySQL tools you're familiar with.

There are several suggested methods for the MSSQL -> MySQL dump, so get to take your pick.

Answer 2

You cannot use mysql client to connect to mssql server. MSSQL is a Windows based SQL database service while MySQL is a different protocol altogether and MSSQL is a different protocol altogether. It is the TDS protocol that seperates the both of them. If you want to connect to MSSQL service from a tool on Unix, then there are bunch of them. You can use FreeTDS, PyMSSQL, ODBC/JDBC approach.

If you are looking for some ready made approach tools then there are few I know:

http://sqldump.sourceforge.net/

http://sqlbackupandftp.com/

Take a look at them.

Flimzy,

mysqldump utility supports database backup files to the local disk location, but mssql or sybase do not seem to offer this flexibility directly. In MSSQL, however one can write the database backup files to UNC paths like network shares, which are otherwise can be called as remote filesystem path mounted locally on the mssql database server. There is a support ftp as well directly from the BACKUP DATABASE SQL command itself, so one can upload the database backup files as a stream to the ftp upload location. The bandwidth differences and protocol limitations are to be taken into perspective, though. It would be good to use UNC paths if you can setup a winbind/smb share on your local machine and can make it available across the workgroup/AD domain and then accessible to MSSQL server; provided there are network bandwidth limitations. If it is local, I would go for it making sure all the security elements are covered.

If it is ftp, it would be simple, since ftp administration is completely local and you can restrict the upload path in the ftp filesystem area. You need not worry of samba joining/AD user requirements and all. Just ftp service as pure network location with perhaps, security permissions enforced. BTW, make sure your dumps are secured, if it is production, I would be doubly sure to make them secure :-)