mysqldump-like script for Microsoft SQL
Download the first beta from here: GetMsSqlDump v0.1
What is GetMsSqlDump?
This tool is the Windows PowerShell – Microsoft SQL Server counterpart of the practical mysqldump (which is in C for mysql). Using this script you can dump your database content into a file in a form of insert statements. The idea behind the creation is the following: If you have a database on Microsoft SQL Server and you want to save/transfer data, you have more options:
- You can create a backup easily – but you can’t read its content and if you need just a few lines or a single table from it, you have to restore the whole database.
- You can export the data with DTS/SSIS – but it’s a bit more complex and you need to specify the file format and SSIS is a bit overkill for moving 23 lines from a server to another without network connection.
- You can sit and envy those guys using MySQL who export their database into a bunch of INSERT INTO statements, making the data easy to read, transfer or even manipulate. Now this is over.
With GetMsSqlDump you can export your data into… er… a bunch of INSERT INTO statements, making the data easy to read, transfer or even manipulate. Sounds familiar, uh?
Who and how can benefit from this tool?
- Sysadmins can move configuration parameters stored in database easily between servers or sites, or even add the values to a disaster recovery plan or deployment note.
- Developers can deliver the initial database content with their database creation scripts without figuring out some dirty hack.
Also, it provides a convenient option to modify the content during transfer.
What makes it so great?
Well, you’ll figure it out at the very first time you have to move some data (5-50000 rows) between servers which are not connected and the data is just part of the database or the target server is managed by a different person who doesn’t trust others’ database backups. If your data is in 5 tables and it’s a one-off data transfer, you won’t be extremely happy to use SSIS as it’s more complex than you would want to do it two-times-five-times (even with the simplified export-import wizard). Transferring the database backup raises some security issues on both ends – the sender doesn’t necessarily wants to reveal all the info in the database. Hiring 13 contractor to type over the records can be a great management suggestion, but let’s not evaluate it now.
Why Microsoft didn’t implement it if it’s so great?
I guess this feature is not in the Microsoft SQL Server by default because they planned it to handle huge databases with huge amount of data where using this kind of tool for backup would be extremely inefficient (oh, yes, I might forgot to mention – mysqldump is the backup tool of MySQL, and for a long time, it was basically the only way to back up your MySQL database).
If you put this question, you probably never used it. It’s too productive to not create scripts with it… But to make you feel more comfortable, there’s also a C# command-line version in the queue.
Why is it called GetMsSqlDump?
Well, it should have been just simlpy mssqldump but this name belongs to an abandoned project which was created with the same goal, just it was written in PHP. So I had to find another name. As PowerShell is full of Get/Set cmdlets, I found out this very fancy name. It links it a bit to PowerShell, however, I’d like to create the C# version with the very same name and if God helps (and my workplace permits), also a port to Linux (who the heck uses MSSQL from Linux? I mean, besides us).