THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Backup a Single Table in SQL Server using SSMS

Our buddy Buck Woody made an interesting post about a common question: "How do I back up a single table in SQL Server?"

That got me thinking about what a backup of a table really is. BCP is often used to get the data but you want the schema as well.

For reasonable-sized tables, the easiest way to do this now is to create a script using SQL Server Management Studio. To do this, you:

1. Right-click the database (note not the table)

2. Choose Tasks > Generate Scripts

3. In the Choose Objects pane, select the table you want to script

4. In the Set Scripting Options pane, click Advanced.

5. In the Types of Data to Script option, choose Schema and Data. (If you also want indexes, etc. make sure they are also chosen)

Click your way through the remaining screens and you're done.

Published Monday, June 07, 2010 6:18 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

cany said:

Hi,

Why I can not see 'Advanced' button in step

4. In the Set Scripting Options pane, click Advanced ?

Thanks.

June 9, 2010 2:45 PM
 

Uzzie said:

Hi Cany,

If you are using SSMS 2008, steps that you need:

- Right click on the DB

- Choose "Tasks" and then "Generate Scripts" and "Script Wizard" dialog box will appear. Then click NEXT.

- Choose DB (don't know why it ask you again?? :P) and NEXT.

- You are at "Choose Script Options" page now. Scroll down to "Table/View Options" and you'll see "Script Data". Set to TRUE if you want to backup the data in the table too. You can also choose other options as you wish. Hit NEXT.

- Select object you want to script.

- Follow the rest of the instruction.

Cheers,

Uzzie

June 10, 2010 9:58 PM
 

Deepika said:

Helpful!!!Thanks:)

August 5, 2011 5:32 AM
 

Mahant said:

Thanks, it helped.

August 9, 2011 12:56 PM
 

Hammad said:

Does not work in MSSQL Management Studio 2005. It scripts only structure and schema no data.

October 14, 2011 4:36 PM
 

Meesham said:

Thanks a lot. It helped me take my table backups!

April 9, 2012 8:05 PM
 

Ajit said:

Is there any query to take bakcup of table and database.

May 4, 2012 6:21 AM
 

Dinesh Vishe said:

if i want take backup 3-4 table then please suggest

June 28, 2012 2:53 AM
 

Greg Low said:

You can just script those 3 tables.

June 28, 2012 3:23 AM
 

Aravindh said:

It should not be work in mysql 2000

June 28, 2012 3:24 AM
 

Greg Low said:

I can't comment on mysql

June 28, 2012 4:54 AM
 

Greg Low said:

Have you considered using Integration Services? That's the type of repetitive task that it's designed to do.

July 14, 2012 3:04 AM
 

Sunil said:

Hi Greg

i am using Sql azure where Integration Services is not available

Thanks

July 20, 2012 11:21 AM
 

Greg Low said:

Hi Sunil,

Yes, but a local copy of Integration Services can connect to SQL Azure just the same. Or are you trying to copy the table to Azure storage or something? (And is it large?)

Regards,

Greg

July 20, 2012 7:31 PM
 

senthilsjc said:

we can use this method for small size of table only

December 13, 2012 11:48 AM
 

pavan said:

how to retrieve table information

December 28, 2012 3:03 AM
 

sathish said:

hey man i tried this but the script generated was to create table is it like after generating script should i change the command to backup and run!!!!!! pls advise...

February 24, 2013 6:38 PM
 

Greg Low said:

Sorry, don't follow. There are scripting options for creating (or dropping) the table, and/or for including the data when generating the script. This is just a way of scripting a table with it's data in it. You then need to use the script that's generated on another DB.

February 24, 2013 7:10 PM
 

ashwini said:

Guys in MSSQL 5 use

select * into NewTableName from ExistingTableName

make sure NewTableName  is not available in DB and ExistingTableName is already available in DB.

This query create a new table with schema of existing table as well as dump complete data.

April 11, 2013 1:42 AM
 

Greg Low said:

Hi Ashwini,

That creates a new table with the same contents of the old one, not a backup of the table. (Backups can be taken elsewhere).

April 25, 2013 9:14 PM
 

Siva said:

Thanks a lot.. It woprked like charm

September 5, 2013 11:18 AM
 

rahul jain said:

thanx it's help a lot

September 13, 2013 3:06 AM
 

Naga B said:

select * into NewTableName from ExistingTableName

It will not create Constraints and keys on new table.

July 23, 2014 9:56 AM
 

Greg Low said:

Yes, if all you want to do is copy the table's data to a new table, that works fine. But it won't help with moving the data elsewhere

July 23, 2014 6:27 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement