Backup a single table with its data from a database in sql server 2008

Multi tool use
Multi tool use
The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP


Backup a single table with its data from a database in sql server 2008



I want to get a backup of a single table with its data from a database in SQL Server using a script.



How can I do that?





SQL Import/Export Wizard. Right click on your database in SMSS/ Choose Item Export
– realnumber3012
Oct 31 '13 at 4:18







I want do this with script
– EBS
Oct 31 '13 at 4:21





please accept MGOwen's Answer
– greg121
Nov 9 '15 at 14:16




9 Answers
9



There are many ways you can take back of table.





While this code may answer the question, providing additional context regarding why and/or how it answers the question would significantly improve its long-term value. Please edit your answer to add some explanation.
– CodeMouse92
Apr 25 '16 at 23:46


select * into mytable_backup from mytable



Makes a copy of table mytable, and every row in it, called mytable_backup.





It's nice that this query will actually create the "mytable_backup" table. I wasn't expecting that, and had created a new table to backup to.
– AidanO
May 26 '17 at 9:19





I used this alot backing up data to a different schema, and I just noticed that under certain circumstances, there are rows MISSING ! The target could be incomplete. I am not sure why. Better to check the row count after such backup.
– Ben
Feb 25 at 1:41



You can create table script along with its data using following steps:



For more information, see Eric Johnson's blog.



Try using the following query which will create Respective table in same or other DB ("DataBase").


SELECT * INTO DataBase.dbo.BackUpTable FROM SourceDataBase.dbo.SourceTable



You can use the "Generate script for database objects" feature on SSMS.



This one solved my challenge.
Hope this will help you as well.



Put the table in its own filegroup. You can then use regular SQL Server built in backup to backup the filegroup in which in effect backs up the table.



To backup a filegroup see:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-files-and-filegroups-sql-server



To create a table on a non-default filegroup (its easy) see:
Create a table on a filegroup other than the default



Another approach you can take if you need to back up a single table out of multiple tables in a database is:



Generate script of specific table(s) from a database (Right-click database, click Task > Generate Scripts...



Run the script in the query editor. You must change/add the first line (USE DatabaseName) in the script to a new database, to avoid getting the "Database already exists" error.



Right-click on the newly created database, and click on Task > Back Up...
The backup will contain the selected table(s) from the original database.



To get a copy in a file on the local file-system, this rickety utility from the Windows start button menu worked:
"C:Program Files (x86)Microsoft SQL Server110DTSBinnDTSWizard.exe"



This query run for me ( for MySQL). mytable_backup must be present before this query run.


insert into mytable_backup select * from mytable





The question is about SQL Server not MySQL.
– Stephen Kennedy
Feb 26 at 12:54




Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).


Would you like to answer one of these unanswered questions instead?

SwQnNKEOZaCGBWCmXDRP,dq7k5h3Bg 3ECWGvgd NpSDkkq2W m,73WjXNIUfvwFUac6QA6hWrSStgtkDiiF6TboI 0 T8gH8Ziwf
3NKQ4B0 WKdiom kmGqZxak HKwxNQZVGw KQ2L b,ejn0AxoVJls,0J3c21N7n,79BM9q,Gv8ofRUx9SsCl,83M

Popular posts from this blog

Visual Studio Code: How to configure includePath for better IntelliSense results

Spring cloud config client Could not locate PropertySource

Makefile test if variable is not empty