Storing your SQL Server Database as Text

Have you ever had trouble moving a database from one server to another?  Ever wanted to store the database in source control?  Storing the database as text (a SQL Server Script) enables plain text tools to operate on your database.

Jeff McWherter from Gravity Works showed me how to easily store a SQL Server Database as Text:

1. In SQL Server Management Studio right click on a database
2. However over Tasks
3. Select Generate Scripts
4. Click 'Set Scripting Options' to skip the first two steps.
5. Click the 'Advanced' button
6. For the last option in general change 'Types of data to script' from schema only to 'Schema and data'
7. Click OK to close the Advanced dialog
8. Select a location to save the script to
9. Click Next to go to summary.
10. Click Next for SQL Server Management Studio to generate the script.
11. Click Finish and you've got your text!
    
12. When executing this script
1. Ensure the Filename in the CREATE DATABASE Statement is correct when executing this script. (Or you can comment out everything after CREATE DATABASE $YOURDBNAME$ until the first GO to have SQL Server use defaults)
2. Since this works at the SQL Server level, you will get errors when the script attempts to create users that don't exist on the target server and then errors when it authorizes that user.

P.S. See my post SQL Server Management Studio Generating Change Scripts if you want to use SQL Server Management Studio to graphically modify the database but then script the change.