backup just a table

sql server 2012

a Developer wants to delete a table in our production database and then run a script to recreate it.
Is there a way to backup just a table in the database?
if not, recommendations on how to proceed with this?
jamesmetcalf74Asked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
In SQL Server you can't backup only an object. You backup the full database.
For backup tables you can create a copy of it as mentioned above or export the data to a file. If you are going to change the table structure you should script the table before making the changes.
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Depending upon the size of the table, couldn't you just take a copy of it ?

select * into mytable_backup from mytable;

Now, you've got possible issues with updates that might occur while the data is being copied (if it's a highly volatile table) but at least you'll have your original ...
0
 
jamesmetcalf74Author Commented:
sorry I'm new to sql.

the two tables you referenced in your solution.

mytable_backup   is this a table you first create before you issue this command or is it created on the fly with the command.
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
The syntax "select * into mytable_backup" will create a new table on the fly based upon the results of the query.

It fails if the table already exists.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.