Generate script in SQL Server 2005 with data

Question

How can I generate a script in SQL Server 2005 with data in table?

Answer 1

Unfortunately the options to script data along with schema didn't seem to appear until SQL Server 2008, so I think you're stuck with third party options.

• SSMS Tool Pack has a generate inserts option
• Narayana Vyas Kondreddi has an excellent generate inserts script I've been using for a few years now

Answer 2

Use data publishing wizard. step by step explanation at :
http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Answer 3

I had the same problem before, and there seems to be no direct script can be made to take all tables structures with their data.

I came around this situation by doing the following:

1. Generate a script for database architecture (RightClick on Database > Tasks > Generate Scripts > Select all tables, and script to a file)
2. Create a backup file for the database (RightClick on Database > Tasks > Backup)

Then, to take your database somewhere else:

1. Execute the database script file to create tables
2. Restore the database using [Database Name] > Restore > Database.

This worked just fine for me using SQL Server 2005.