Backing Up SQL Table Structure and Data - Export Data and Script Table

This is the second post in a series about Backing Up SQL Table Structure and Data. It's the first post in this series where we will dive into one of the methods that I find myself commonly using. That method is something I have dubbed Export Data and Script Table.

I'll show you how to do it and then briefly discuss the pros and cons of archiving SQL tables this way.

Step 1 is to export all the data. To that follow these steps:

1. Open the server in your object explorer.
2. Right click on the database.
3. Select Tasks then Export Data.
4. Export the data from the table you choose in the format you choose.

Step 2 is to script the table so you can re-create it.

1. Right click on the table in your object explorer.
2. Click Script Table as | CREATE To | New Query Editor Window
3. Save the file.

After that I would usually do a couple of things to make sure everything is working correctly. I would run the table creation script and import the data file into it. If that worked correctly then I would compare the data in the new table to the old table and check to make sure everything looks correct. After that I would go ahead and drop both tables knowing that I have an operational backup should I need it.

Pros:

• Fairly easy to do for one table
• Data can be read outside of SQL
• Table script can be reviewed and is relatively small file

Cons:

• Gets tedious when backing up multiple files (or re-create multiple tables)
• Lots of files to keep track of

So with that you'll have to make your own decision if this is the right method for you. Keep watching for the other ways to back up your data and table structure from SQL.