|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Restoring a column in a tableGuys,
One of our collegues deleted a colum in the table of the database. Is there anyway I can just restore that column only without restoring the whole database. I remember doing something like this in the past but it has been a long time ago. Can I restore the database to a different name? From the restored database import the column and data into the live database? The column was deleted on Friday. We are looking to restore it this coming week. Thanks, Tony Hi Tony,
You cannot restore only a column. Maybe some specialized backup/restore tools can do that, but not the tools that come integrated with SQL Server. Here is a scenario to restore the column: - Use ALTER TABLE ... ADD ... to add the column to the original table. You can also use the graphical interface. - Restore the database that has the column under a different database name (yes, you can do that). - Run UPDATE query to update the original table with the values from the restored database. HTH, Plamen Ratchev http://www.SQLStudio.com We are using EMC Legato. It prompts me for recovery mode.
I have optioin for Normal, No Recovery, Standby. Which one do we choose? Tnt Show quote "Plamen Ratchev" wrote: > Hi Tony, > > You cannot restore only a column. Maybe some specialized backup/restore > tools can do that, but not the tools that come integrated with SQL Server. > > Here is a scenario to restore the column: > > - Use ALTER TABLE ... ADD ... to add the column to the original table. You > can also use the graphical interface. > - Restore the database that has the column under a different database name > (yes, you can do that). > - Run UPDATE query to update the original table with the values from the > restored database. > > HTH, > > Plamen Ratchev > http://www.SQLStudio.com > > > As SQL Server terms they mean as the following:
No Recovery = When you set a database' s recovery state as No Recovery, then you'll not be able to query your database but it's going to be "Restoring...". Standby = When you set a database' s recovery state as Standby, then your database will be online and can be queriable. However it's going to be Read-Only. Normal = Must be "Recovery" and it means an "Online" database. This is what we use under normal circumstances =) So, in your case it'd be better to set it to Normal I guess. -- Ekrem Önsoy Show quote "tnt" <t**@discussions.microsoft.com> wrote in message news:B4009E49-7489-494B-8051-C98666BC9DC1@microsoft.com... > We are using EMC Legato. It prompts me for recovery mode. > > I have optioin for Normal, No Recovery, Standby. > > Which one do we choose? > > Tnt > > "Plamen Ratchev" wrote: > >> Hi Tony, >> >> You cannot restore only a column. Maybe some specialized backup/restore >> tools can do that, but not the tools that come integrated with SQL >> Server. >> >> Here is a scenario to restore the column: >> >> - Use ALTER TABLE ... ADD ... to add the column to the original table. >> You >> can also use the graphical interface. >> - Restore the database that has the column under a different database >> name >> (yes, you can do that). >> - Run UPDATE query to update the original table with the values from the >> restored database. >> >> HTH, >> >> Plamen Ratchev >> http://www.SQLStudio.com >> >> >> Thanks guys.
I went ahead and use "Normal" since I am giving a different name anyways. I just remember standby is similiar to the log shipping approach. Tnt Show quote "Ekrem Önsoy" wrote: > As SQL Server terms they mean as the following: > > No Recovery = When you set a database' s recovery state as No Recovery, then > you'll not be able to query your database but it's going to be > "Restoring...". > Standby = When you set a database' s recovery state as Standby, then your > database will be online and can be queriable. However it's going to be > Read-Only. > Normal = Must be "Recovery" and it means an "Online" database. This is what > we use under normal circumstances =) > > So, in your case it'd be better to set it to Normal I guess. > > -- > Ekrem Önsoy > > > > "tnt" <t**@discussions.microsoft.com> wrote in message > news:B4009E49-7489-494B-8051-C98666BC9DC1@microsoft.com... > > We are using EMC Legato. It prompts me for recovery mode. > > > > I have optioin for Normal, No Recovery, Standby. > > > > Which one do we choose? > > > > Tnt > > > > "Plamen Ratchev" wrote: > > > >> Hi Tony, > >> > >> You cannot restore only a column. Maybe some specialized backup/restore > >> tools can do that, but not the tools that come integrated with SQL > >> Server. > >> > >> Here is a scenario to restore the column: > >> > >> - Use ALTER TABLE ... ADD ... to add the column to the original table. > >> You > >> can also use the graphical interface. > >> - Restore the database that has the column under a different database > >> name > >> (yes, you can do that). > >> - Run UPDATE query to update the original table with the values from the > >> restored database. > >> > >> HTH, > >> > >> Plamen Ratchev > >> http://www.SQLStudio.com > >> > >> > >> > Yea, you can use NORECOVERY or STANDBY when you set up a log shipping
solution. However, when you set your database' s recovery state Standby then your users will be able to query your tables however they' ll not be able to delete\update\insert. And when a new transaction comes from the Principal then all your connected users would be disconnected to apply the new transaction against your database when you set your database' s recovery model as Standby. -- Ekrem Önsoy Show quote "tnt" <t**@discussions.microsoft.com> wrote in message news:B863E746-820E-466B-B986-7735DE6AD580@microsoft.com... > > Thanks guys. > > I went ahead and use "Normal" since I am giving a different name anyways. > > I just remember standby is similiar to the log shipping approach. > > > Tnt > > "Ekrem Önsoy" wrote: > >> As SQL Server terms they mean as the following: >> >> No Recovery = When you set a database' s recovery state as No Recovery, >> then >> you'll not be able to query your database but it's going to be >> "Restoring...". >> Standby = When you set a database' s recovery state as Standby, then your >> database will be online and can be queriable. However it's going to be >> Read-Only. >> Normal = Must be "Recovery" and it means an "Online" database. This is >> what >> we use under normal circumstances =) >> >> So, in your case it'd be better to set it to Normal I guess. >> >> -- >> Ekrem Önsoy >> >> >> >> "tnt" <t**@discussions.microsoft.com> wrote in message >> news:B4009E49-7489-494B-8051-C98666BC9DC1@microsoft.com... >> > We are using EMC Legato. It prompts me for recovery mode. >> > >> > I have optioin for Normal, No Recovery, Standby. >> > >> > Which one do we choose? >> > >> > Tnt >> > >> > "Plamen Ratchev" wrote: >> > >> >> Hi Tony, >> >> >> >> You cannot restore only a column. Maybe some specialized >> >> backup/restore >> >> tools can do that, but not the tools that come integrated with SQL >> >> Server. >> >> >> >> Here is a scenario to restore the column: >> >> >> >> - Use ALTER TABLE ... ADD ... to add the column to the original table. >> >> You >> >> can also use the graphical interface. >> >> - Restore the database that has the column under a different database >> >> name >> >> (yes, you can do that). >> >> - Run UPDATE query to update the original table with the values from >> >> the >> >> restored database. >> >> >> >> HTH, >> >> >> >> Plamen Ratchev >> >> http://www.SQLStudio.com >> >> >> >> >> >> >> I am really not familiar with EMC Legato, but as long as you can specify a
different database name to restore to, the Normal option sounds good to me. It probably corresponds to the regular SQL Server restore with recovery. HTH, Plamen Ratchev http://www.SQLStudio.com Dear Tony,
Restore the database in some other name . Alter the table in the current database And add the Deleted column .Then Updated the Data of the column from the restored Database .I think it is very easy Regards Prasad On Nov 26, 10:18 am, tnt <t***@discussions.microsoft.com> wrote: Show quote > Guys, > > One of our collegues deleted a colum in the table of the database. Is there > anyway I can just restore that column only without restoring the whole > database. > > I remember doing something like this in the past but it has been a long time > ago. > > Can I restore the database to a different name? From the restored database > import the column and data into the live database? > > The column was deleted on Friday. We are looking to restore it this coming > week. > > Thanks, > Tony  |
|||||||||||||||||||||||
Other interesting topics