Backing Up SQL Table Structure and Data - Script Database

This is the fourth post in a series about Backing Up SQL Table Structure and Data. It's the third 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 Script Database.

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

There's only one step to this method. To that follow these steps:

1. Open the server in your object explorer.
2. Right click on the database.
3. Select Tasks then Generate Scripts.
   
4. Select the database that contains the data to be exported. Click next.
5. Under the Table/View Options section make sure Script Data is set to True. Click next.
   
   
6. Mark the check box for Tables. Click next.
7. Mark the check boxes next to the tables to script structure and data. Click next.
8. Select Script to file and enter a folder location, files to generate should be set to file per object, save as can be either depending on the data (I leave mine at unicode text usually). Click finish.
   
9. Check to make sure everything exported to the directory you listed.

Pros:

• Same amount of effort for one or multiple tables
• Quick
• Everything contained in one file for each table

Cons:

• Relatively large files depending on how many columns/rows each table has
• No was to review the data in a meaningful way outside of rebuilding the table and inserting the table

So with that you'll have to make your own decision if this is the right method for you.