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?
Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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?

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck