Sune Rievers blog

Found this today: http://vyaskn.tripod.com/code.htm#inserts

Narayana Vyas Kondreddi has created a stored procedure, that dumps all data from a MS-SQL database. It works like a charm! I think this is one of the few features where MySQL is actually ahead of MS-SQL, via the mysqldump tool. This makes backup and server transitions a breeze, when dealing with MySQL, and now also MS-SQL (sort of). Only inserts are supported, and there is no checking for existing data.

The script is really easy to use, after creating the SP, you run this sql query: EXEC sp_generate_inserts 'titles' to dump all data from the ‘titles’ table. It also allows some customization, and reducing the amount of exported data, eg using sql where-statements.

To export all data from all tables in the database, run:

SELECT 'EXEC sp_generate_inserts ' +
'[' + name + ']' +
',@owner = ' +
'[' + RTRIM(USER_NAME(uid)) + '],' +
'@ommit_images = 1, @disable_constraints = 1'
FROM sysobjects
WHERE type = 'U' AND
OBJECTPROPERTY(id,'ismsshipped') = 0

The entire script is located here for MS-SQL 2005.

You would probably also need to script the db_owner user (when login is already created on the server):

CREATE USER [mydatabase] FOR LOGIN [myuser] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember 'db_owner', 'myuser'
GO