best way to restore table in SQL Server 2005?

Mark
Mark used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Essentially if you have your backupdb available (in Enterprise Edition this could also be a database snapshot), generate scripts from the table by right clicking it and run this script against your target database (beware of the USE statement in the first line).
Next you need to do;
USE myRealDB
GO
SET IDENTITY_INSERT myTable ON
GO
INSERT myTable
SELECT *
FROM backupDB.dbo.myTable
GO
SET IDENTITY_INSERT myTable OFF
GO

Identity_insert isn't needed when there's no identity. The * can be troublesome in some scenarios, but you can create the column list easily by dragging the "columns" folder below the table into the query window (and then delete the troublesome computed columns).

Author

Commented:
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
DBCC CHECKIDENT ('myTable', NORESEED)

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?

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial