Backing Up SQL Table Structure and Data - Bulk Copy Program

This is the third post in a series about Backing Up SQL Table Structure and Data. It's the second 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 (or actually Microsoft has) Bulk Copy Program.

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

There's two steps to this method. First export the data:

1. Create a batch (.bat) file in Windows.
2. Enter the following code inside the batch file:
   bcp "select Col1, Col2, Col3 from Database.user.TableName" queryout c:\bulkcopy\tablename.txt -n -Sserver -Usa -P
   
   Obviously the column names, table and location need to be replaced. After the capital S you can replace the word server with the server that has SQL installed. After the capital U you can replace sa with your user name. After the capital P enter your password.
3. Double click on the batch file and watch it run. Could take a bit if it's a large file.

The second step is to script the table:

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.

Pros:

• Only a little extra effort for one vs multiple tables
• Can run unattended
• Importing data is really easy

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
• Might not have access to this unless you are an admin

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