THE SQL Server Blog Spot on the Web

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

Greg Low (The Bit Bucket: IDisposable)

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

Backup a Single Table in SQL Server using SSMS

This blog has moved! You can find this content at the following new location:

Published Monday, June 7, 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



cany said:


Why I can not see 'Advanced' button in step

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


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.



June 10, 2010 9:58 PM

Deepika said:


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


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?)



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

Arvind said:

It is really very use full for me thank you so much.

July 25, 2014 10:46 AM

ITC said:

It is very usefull, Thanks a lot :)

October 10, 2014 6:03 AM

Juan said:

Thanks for this!

Helped out tremendously

June 18, 2015 12:31 PM

Sarah said:

What size table would be considered small enough to make this the appropriate method?

July 12, 2015 11:43 PM

Greg Low said:

I've used it up to reasonable-sized tables but you need to keep in mind the size of the SQL script generated. That's the main limitation. They get hard to work with when they get large.

July 13, 2015 6:25 AM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement