How to backup a database table as a non-DBA?


I am using a table in a no-production Sql Server database. And I am not the database Admin.
I would like to backup a table (dump the numbers into a file) and then, when/if the table gets accidentally deleted, I can restore it real quick.

Could someone point to any direction on how to do that?

Thanks a lot.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
you can run the below query

select * into YourTable_Bak from YourTable

replace 'yourTable' with the actual table name . the above query will create another table with the same structure and the data, but wont create Indexes or any keys . if you want to create them, you can script the table along the data.
In SSMS: Right click the Database name and select Tasks - Generate scripts from the menu. Then select your table and generate all the commands to restore the table and its data in a text form.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfz2004Author Commented:
Aneesh,I tried "select * into YourTable_Bak from YourTable" but I don't have permission to create a table in the DB.


I am trying the "select Tasks - Generate scripts ". But what is the commands to restore the table and its data in a text term?
I am new to SSMS. Thanks.

SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Aneesh RetnakaranDatabase AdministratorCommented:
>Aneesh,I tried "select * into YourTable_Bak from YourTable" but I don't have permission to create a table in the DB.

You need to get the permission or you can backup the table into a  database where you got permission.  
Tempdb is an option, but once someone restarts the sql server it will go.
jfz2004Author Commented:
How about dump the content of a thable out into something like "INSERT INTO...."?
Aneesh RetnakaranDatabase AdministratorCommented:
If you don't have the permission to create the table where are you planning to put the data ?
jfz2004Author Commented:
OK. I am planning to dump the data into a text file. And if something happens to the database table, I can restore the file into the table. But what I want the file to have many rows, each row is like "INSERT INTO....VALUE....".
So that the restore is easier for me.

Any idea?


The Script generator in Tasks creates exactly the text file you need. Just select your table in the dialog and check the output.

Update: You have to change "Types of data to script" to "Schema and data" in Advanced Scripting Options dialog.
Vitor MontalvãoMSSQL Senior EngineerCommented:
In SQL Server there's no "table backup" but you can export the rows from a table to a text file or another file type (csv, excel, ...). For that right-click on the database name, chose Tasks and Export Data... Now just follow the wizard.
jfz2004Author Commented:
Thanks a lot.

I am trying the general script, but I keep getting errors at the last step.
I am attaching two images of setting. Would you take a look at it and see
what could be the problem?



Vitor MontalvãoMSSQL Senior EngineerCommented:
Scripting a table doesn't export the data. You can still script the table in the case you need to recreate it in the future but will be an empty table. You still need to export the data for later import.
The error could mean you have insufficient access rights. If you Save the Report then you may read more info about the error.

The solution should be to ask your admin to give you sufficient access rights to be able to Generate the script from your table and its data.

Another solution is to generate the script yourself by SQL SELECT command. Let say you have one integer and one varchar(20) column in your table. Then you may script the data by following command:
SELECT 'INSERT INTO YourTable (colID, colDescription) VALUES (' + 
    CAST(colID as varchar(20)) + ', ''' + colDescription + ''')'
  FROM YourTable

Open in new window

And you may save the output to a file and the script is done. Of course, above command does not allow to have apostrophes in the varchar column but you may simply convert them:
SELECT 'INSERT INTO YourTable (colID, colDescription) VALUES (' + 
    CAST(colID as varchar(20)) + ', ''' + REPLACE(colDescription,"'", "''") + ''')'
  FROM YourTable

Open in new window

Above REPLACE function (which duplicates apostrophes in the varchar data) can be updated to
REPLACE(colDescription,CHAR(39), CHAR(39)+CHAR(39))
which does not require to change the QUOTED_IDENTIFIER setting.
jfz2004Author Commented:
Thanks everyone, let me test one by one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.