Author |
Topic |
ayu
Starting Member
43 Posts |
Posted - 2008-07-15 : 08:37:30
|
hi folks,by mistake..i drop one table and now i created again that tablebut i want all datas.. means records...so how can i will get it? means from any backup files..can anyone suggest me...plzthanks |
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-07-15 : 08:40:27
|
Do you have a backup of the database that would contain the data? If so you'd need to restore the old database under a different name than your prod database either to the same server or a different server. Then extract the data and transfer it into the production table. If you don't have a backup of the database you're out of luck. Once you drop it it's gone.Mike"oh, that monkey is going to pay" |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-07-15 : 08:40:52
|
Restore the latest data backup to new database and then copy records to the newly created table in the original database.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
ayu
Starting Member
43 Posts |
Posted - 2008-07-15 : 08:43:21
|
harsh, can u tell me..in detail..i have database - nhlbi..plz its urgent..i m scared..thanksUSE [nhlbi]GO/****** Object: Table [dbo].[myContactLog] Script Date: 07/15/2008 08:22:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[myContactLog]( [contact_id] [int] NOT NULL, [contact_date] datetime NULL, [contact] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [future_commitments] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [filled_to_date] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-15 : 08:55:40
|
quote: Originally posted by ayu harsh, can u tell me..in detail..i have database - nhlbi..plz its urgent..i m scared..thanksUSE [nhlbi]GO/****** Object: Table [dbo].[myContactLog] Script Date: 07/15/2008 08:22:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[myContactLog]( [contact_id] [int] NOT NULL, [contact_date] datetime NULL, [contact] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [future_commitments] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [filled_to_date] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF
Do you have latest backup of the database?MadhivananFailing to plan is Planning to fail |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-07-15 : 08:56:07
|
Myself and mfemenel have already listed steps.1. Restore latest backup under new database name, preferrably same server2. Ensure the new database is created and table you wanted restore is there with proper data3. Now copy the data from newly created database to original database table (whose data is missing) using either simple INSERT INTO...SELECT T-SQL or SSIS package.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
ayu
Starting Member
43 Posts |
Posted - 2008-07-15 : 11:11:32
|
Harsh,i copied into different database now i have to move this table or copy this table to original database with insert statement how can i do?thanks for ur hlep..USE [nhlbitemp]GO/****** Object: Table [dbo].[myContactLog] Script Date: 07/15/2008 08:22:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[myContactLog]( [contact_id] [int] NOT NULL, [contact_date] datetime NULL, [contact] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [future_commitments] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [filled_to_date] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-07-15 : 12:44:33
|
download "microsoft database publishing wizard" and script out the table data from your restored database. It will create Insert statements which you can run in your production database. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-15 : 23:25:14
|
>> now i have to move this table or copy this table to original database with insert statement how can i do?Use 'insert into ... select ... from ...' statement, you can find details in books online. Can also use data import/export wizard or ssis copy db object task. |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-07-16 : 10:08:43
|
he should be having read-only rights at the first place. |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-07-16 : 11:26:48
|
Here are the steps that I would do (THis is what Harsh Suggested)1. Restore a backup copy that has the information you needRESTORE DATABASE MYDBBACKUP FROM DISK = 'C:\MyOriginalDb.bak' --The backup File WITH REPLACE, NORECOVERY, -- Use if more T/Logs to recover MOVE 'MyOriginalDB_Data' TO 'C:\Program Files\Microsoft SQL Server\DataFiles\MYDBBACKUP.mdf', MOVE 'MyOriginalDB_Log' TO 'C:\Program Files\Microsoft SQL Server\DataFiles\MYDBBACKUP_Log.ldf' Change the names of your DB accordingly, but basically the above code will create a NEW DB called MYDBBackup from the 'MyOriginalDB' Database backup file that has the information you need in it. Make sure the names are accurate of your data and log files, otherwise you will get a error.2. Verify the table and data exist in the backup DB created, and it is the way you want3. Delete the table in the New DB that you created (I say to do this just because since you have the backup db now functioning, I would use that to create the table to ensure nothing is missed)4. In SSMS Open the Backup db and RIGHT CLICK on the table you want to replace -> then select "Script Table as" -> New Query window. (This will put in the exact code to duplicate the table 100% in the right hand query window. Change the first line in that window so it says USE [MYOriginalDB]--Type Your Active DB name here[code]Once that is done all that is left is you need to copy the data from the back to your original. To do this just do a [code]Insert into MyOriginalDB.dbo.myContactLog(Col1,col2,etc)Select Col1,Col2,etc fromMyDBBackup.dbo.myContactLog Hope this helps. |
|
|
|