Exporting a SQL Server 2005 Database to a Script

How do we script our SQL Server 2005 database? This was a question we needed to answer today. It surprised me that the answer was not obvious after a quick search on Google. I thought I should write about what we did, so I would not forget and others might learn.Microsoft SQL Server Database Publishing WizardAfter a couple of false starts with other tools, we found the Microsoft SQL Server Database Publishing Wizard. The wizard walks you through the process of creating a script file to recreate a database. It works as advertised except for the following gotchas.Publishing ViewsThe wizard tries to drop and recreate all tables that are referenced in the view. This may be what you want, but it was not what we wanted.Publishing Large Amounts of DataThe wizard generates one script file that can be used to recreate the parts of a database that you choose. The file that gets created by the wizard can be quite large (our largest is 600+MB). Large files will not open in the SQL Server Management Studio. SQLCMD can be used on the large files.If anyone else has any tips for scripting a SQL Server 2005, I would love to hear them.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: