Solved

backup just a table

Posted on 2015-01-14
4
54 Views
Last Modified: 2015-01-20
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?
0
Comment
Question by:jamesmetcalf74
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 333 total points
Comment Utility
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
 

Author Comment

by:jamesmetcalf74
Comment Utility
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
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 333 total points
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 167 total points
Comment Utility
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now