Getting MS SQL Server Database Dump

Coming from MySQL and PostgreSQL background I am used to tools such as mysqldump for getting a complete dump of an SQL database into an a file. The file then contains SQL commands for creating the database structure and more importantly, for filling it with data using INSERT statements. A nice feature indeed.

Since I've begun working with Microsoft SQL Server, it has always been a problem for me to get the same result. There is no command line utility that I know of that comes with SQL Server 2000 that allows you make the same dump as mysqldump provides. There is however a hidden feature in the Enterprise Manager that you can use to achieve the more or less same result as with mysqldump. I have written about how to get the database schema using Enterprise Manager some time ago. The problem is that it does not work for SQL Server 2005 - there is no Enterprise Manager for v2005! The Microsoft SQL Server Management Studio does not have that useful feature so we are left alone... Or maybe not.

Some time ago I was determined to find a solution for this problem. So I have searched high and searched low, and I have found something. The thing is the Database Publishing Wizard. It does exactly what is needed - it dumps the schema, the data and even stored procedures if any are available! It has a command line interface, a GUI interface and on top of it it integrates with Visual Studio!

What more can you want?

Add to del.icio.us