There is a Database Publishing Wizard 1.1 you can download that may work for SQL Server 2005, but didn’t work for me with SQL Server 2008. However,Database Publishing Wizard 1.3 is installed with Visual Studio 2008 but I cannot find a separate download. This tool gets you the schema and data and everything but the “drop and create database” script.
So I think you need Visual Studio 2008 for this for SQL Server 2008 to get it. I am not sure why I cannot find it separately. Maybe Microsoft has a reason.
Step 1 – In Visual Studio 2008, go to Tools | Connect to Database and connect to a MS SQL database.
Under the Server Explorer window, the connection now appears.
Step 2 – Expand Data Connections.
Step 3 – Right-click on the connection and choose Publish to provider.
Step 4 – Click Next.
Step 5 – Choose the database.
Step 6 – Click Next.
Step 7 – Select the publishing options (such as to export the schema and data or just the schema).
Step 8 – Choose a file.
Step 9 – Click Finish.
Step 10 – The one thing this is missing is the script to drop and create the database. You can easily get this from Microsoft SQL Server Management Studio 2008 (there is a free Express version if you don’t have it). Just connect to the database, right-click on the database and choose Script Database as | Drop And Create to | Clipboard. Now past this text to the top of your file you just created.