|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
IDENTITY and Backup/RestoreSS2K: 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/ Is it really just that straight-forward? (specifically inquiring about
restoring the original IDENTITY values here). Thanks! Show quote "SQL" <denis.g***@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. Show quote "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.g***@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/ >> > > 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: Show quote > 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! > >  |
|||||||||||||||||||||||
Other interesting topics