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.
Hi,
Why I can not see 'Advanced' button in step
4. In the Set Scripting Options pane, click Advanced ?
Thanks.
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
Helpful!!!Thanks:)
Thanks, it helped.
Does not work in MSSQL Management Studio 2005. It scripts only structure and schema no data.
Thanks a lot. It helped me take my table backups!
Is there any query to take bakcup of table and database.
if i want take backup 3-4 table then please suggest
You can just script those 3 tables.
It should not be work in mysql 2000
I can't comment on mysql
Have you considered using Integration Services? That's the type of repetitive task that it's designed to do.
Hi Greg
i am using Sql azure where Integration Services is not available
Thanks
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
we can use this method for small size of table only
how to retrieve table information
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…
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.
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.
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).
Thanks a lot.. It woprked like charm
thanx it's help a lot
select * into NewTableName from ExistingTableName
It will not create Constraints and keys on new table.
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
It is really very use full for me thank you so much.
It is very usefull, Thanks a lot 🙂
Thanks for this!
Helped out tremendously
What size table would be considered small enough to make this the appropriate method?
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.