Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 drop table but not deleted

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 table
but i want all datas.. means records...so how can i will get it? means from any backup files..can anyone suggest me...plz

thanks

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"
Go to Top of Page

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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

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..thanks

USE [nhlbi]
GO
/****** Object: Table [dbo].[myContactLog] Script Date: 07/15/2008 08:22:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF
Go to Top of Page

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..thanks

USE [nhlbi]
GO
/****** Object: Table [dbo].[myContactLog] Script Date: 07/15/2008 08:22:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF


Do you have latest backup of the database?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 server
2. Ensure the new database is created and table you wanted restore is there with proper data
3. 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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 need

RESTORE 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 want

3. 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
from
MyDBBackup.dbo.myContactLog


Hope this helps.

Go to Top of Page
   

- Advertisement -