Link to home
Start Free TrialLog in
Avatar of Mark

asked on

best way to restore table in SQL Server 2005?

I want to restore a table in SQL Server 2005, not the whole database. I don't believe I have 'file groups' set up. I am doing:

drop table myTable
select * into dbo.myTable from backupDB.dbo.myTable

the 'restore into' works well enough - it preserves identities and null/not-null settings. The problem is that it loses the key and index definitions. I have not tried it on a table with column descriptions and initial values, so I don't know if those are preserved or not.

Is there a better way that preserves all attributes of a table?
Avatar of dba72
Flag of Netherlands image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark


Well, my source database wasn't actually missing, so instead of scripting the create-table, I guess I could:

delete from myTable
{ do your suggestions here }

then find the new next identity:

use backupDB

insert using INDENTIY_INSERT as you've suggested, then reseed original table with source table's value: DBCC CHECKIDENT ('mytable, RESEED, <valuefromprevDBCC>)

So, either I:

1. script the create-table, drop the table, re-create empty table using script w/index def, etc.; do inserts as you've suggested with IDENTITY_INSERT.

2. Drop table rows, insert from source with IDENTITY_INSERT, then RESEED.

3. Drop the table, use INSERT INTO, then re-create indexes by hand.

Are those my choices? Hmmmm, I was hoping for something simpler; like INSERT INTO but preserving index defintions w/o having to script them out.

I suppose a 4th option/compromise would be to script the create-table, drop the table, use INSERT INTO, then run just the parts of the script to create keys/indexes and possibly column defaults, etc.

Any other thoughts, anybody?
Avatar of Mark


I'm a bit disappointed there wasn't more discussion on this, but oh well.

I've gone with my option 3: Drop the table, use INSERT INTO, then re-create indexes by hand.

However, if I had a table with lots of indexes and if that option also loses the descriptions and initial values (which I didn't test), then I'd use your scripting method. Thanks