IDENTITY and Backup/Restore

SS2K: How to deal with IDENTITY property restore [from backup] situation?
Specifically: table Foo has IDENTITY property which is also used as the
primary key (yes, UNIQUE constraints are on the "real-world" key). Data
entered on November 16 was deleted by user on November 20. It's now December
2 and the user wants it back. The original values to be recovered, including
IDENTITY values, are available in the full backups from November 18.
Thanks!What about restore to another DB and or table
SET IDENTITY_INSERT ON
insert the rows from the other table
SET IDENTITY_INSERT OFF
http://sqlservercode.blogspot.com/|||Sounds like you're restoring to another database and inserting the data
from there into your production data. See SET IDENTITY_INSERT <table> ON
in BOL.
caveats: IDENTITY_INSERT is global - when it's on, it's on for all
sessions, so set it on immediately before the insert/insert batch and
set it off immediately after.
Jeff wrote:
> SS2K: How to deal with IDENTITY property restore [from backup] situation?
> Specifically: table Foo has IDENTITY property which is also used as the
> primary key (yes, UNIQUE constraints are on the "real-world" key). Data
> entered on November 16 was deleted by user on November 20. It's now Decemb
er
> 2 and the user wants it back. The original values to be recovered, includi
ng
> IDENTITY values, are available in the full backups from November 18.
> Thanks!
>|||Is it really just that straight-forward? (specifically inquiring about
restoring the original IDENTITY values here).
Thanks!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1133547433.877455.160210@.z14g2000cwz.googlegroups.com...
> What about restore to another DB and or table
> SET IDENTITY_INSERT ON
> insert the rows from the other table
> SET IDENTITY_INSERT OFF
> http://sqlservercode.blogspot.com/
>|||Yes, its that straight forward, you need to include the column with the
IDENTITY property on the insert clause too.
It doesn't affect your IDENTITY seeding or anything.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jeff" <Jeff@.NoSpam.com> wrote in message
news:etzkG129FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Is it really just that straight-forward? (specifically inquiring about
> restoring the original IDENTITY values here).
> Thanks!
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1133547433.877455.160210@.z14g2000cwz.googlegroups.com...
>