Export tables with data

If you ever work with databases - since you're reading this, I suppose you do - you know that performing certain actions on a database can be tricky. It's risky business and you don't want to wind up losing (someone else's) precious data.

Performing a database backup before executing the foolish, pray-this-works actions would be a solution, but it can be quite a hassle. You'll need permissions and time, two things developers rarely have :P

So, how about we perform a backup, but only of the table that you are about to mess up? Great, but that would bring us to the original problem of permissions and time, unless... you generate a CREATE script with data!


It's a (slightly hidden) feature of SQL Server 2008 which is fast and would require less permissions. But how? When creating a create-script, the option to generate insert-scripts for the data as well is disabled by default. All you'll have to do is change that setting and generate the script.


For SQL Server 2008

• Right-click the database containing the table you were about to mess up.
• Select "Tasks" and choose "Generate scripts".
• A wizard will open in which you can select the correct database.
• On the next page of the wizard you will be shown options on what information the scripts should contain. Find the setting "Script Data" under the "Table/View Options" header and set the value to "True". By default, this will be set to "False".
• Next, select the table or tables of which you don't want to lose the data.
• Set the settings on where the scripts should be stored and you'll be ready to go!

For SQL Server 2008 R2

• Right-click the database containing the table you were about to mess up.
• Select "Tasks" and choose "Generate scripts".
• A wizard will open in which you can select the correct database and the table or tables of which you don't want to lose the data.
• On the next page of the wizard you will be shown options on where the scripts should be stored. You'll find a link called "Advanced options". This contains settings on what information the scripts should contain. Find the setting "Types of data to script" under the "General" header and set the value to "Schema and data". By default, this will be set to "Schema only".
• That's it, review the settings and generate the scripts!

I haven't checked if this option is available in SQL Server 2005, but if not, you could use SQL Server 2008 to connect to your "old" database to generate the scripts you need.

Now you'll have a script that has create statements to create the table with all the necessary keys AND has insert statements for each row in that table. With that script in hands, you're free to mess up you're database knowing that you can always put the original back. Keep in mind that this is not done for production databases, but off course you knew that and wouldn't dare to :)

That's all folks!


Could be useful, right?