how to strore table backup on database

Question

how to strore table backup on database

rajeshn29.dba

Ten Centuries

Points: 1250
More actions
August 12, 2011 at 12:56 pm

#246151

Hi all,
I have one database bank-db in that 10 tables are there i took backup out of 10 tables 2 tables useing this command
emp is my table name
select * into EMP_BKP from EMP
So now i was trancate table also from db ,now i want to restore table backup with in database .................... i don't know how to restore table backup in database can you some help me....................
Raj

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

If you want to use the SQL SELECT INTO Statement to make a copy of the table then you do not want to truncate the table.

Drop the table and perform a SELECT INTO again.

I assuming that you do not care about Indexes, etc? If so then you want to create another table and perform an INSERT INTO.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

homebrew01

SSC Guru

Points: 55548

August 12, 2011 at 1:15 pm

#1368175

insert into EMP

select * from EMP_BKP

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Perhaps I misunderstood but I did not interpret that inserting the records from EMP_BKP into EMP is the desired solution.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Welsh Corgi (8/12/2011)
Perhaps I misunderstood but I did not interpret that inserting the records from EMP_BKP into EMP is the desired solution.

Just as likely that I misunderstood.

I thought that:

1) EMP was backed up into EMP_BKP

2) EMP got truncated

3) Would like to get EMP_BKP data back into EMP ??

Raj, can you clarify what you want to do ?

rajeshn29.dba Ten Centuries Points: 1250 More actions · Answer 5

Can plz send correct SCRIPT it was production server

rajeshn29.dba Ten Centuries Points: 1250 More actions · Answer 6

homebrew01@@

yes you are right

* EMP was backed up into EMP_BKP

* EMP got truncated

*Would like to get EMP_BKP data back into EMP right can you plz tel me script it will be more help to me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

NM

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

If that's the case, this is what you're looking for

homebrew01 (8/12/2011)
insert into EMP
select * from EMP_BKP

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

If you have identity columns that you want to maintain, then you may need to add something like

SET IDENTITY_INSERT BKP ON

insert into EMP (Fld1, Fld2, Fld3)

select * from EMP_BKP

SET IDENTITY_INSERT BKP OFF

Or you can use the Import/Export Data Task in SSMS

rajeshn29.dba Ten Centuries Points: 1250 More actions · Answer 10

i want to take two tables backups ...............can you plz some tell me procedure

homebrew01 SSC Guru Points: 55548 More actions · Answer 11

rajeshn29.dba (8/12/2011)
i want to take two tables backups ...............can you plz some tell me procedure

I don't understand your question.

You already know how to backup a table as you did with EMP to EMP_BKP

If this is production, I suggest you practice in development first.

Also, when I make changes in production, I often do it in a transaction so I can roll back if I make a mistake.

begin tran

update EMP

set status = 'Y'

where status = 'Z'

(100 records affected)

-- Oooops !! I did the wrong code !!!

Rollback

-- try again

begin tran

update EMP

set status = 'Y'

where status = 'X'

(20 records affected)

--Good

commit

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Good thing you had a backup. 🙂

Just wondering but why did you decide to perform a SELECT INTO and subsequently TRUNCATE the table?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

homebrew01 SSC Guru Points: 55548 More actions · Answer 13

Welsh Corgi (8/12/2011)
Good thing you had a backup. 🙂
Just wondering but why did you decide to perform a SELECT INTO and subsequently TRUNCATE the table?

Yes, there are many questions that could be asked.

rajeshn29.dba Ten Centuries Points: 1250 More actions · Answer 14

rajeshn29.dba

Ten Centuries

Points: 1250

August 12, 2011 at 2:54 pm

#1368236

Thanks to all my problem resloved