Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

undo sql alter command

Posted on 2010-11-10
11
Medium Priority
?
445 Views
Last Modified: 2012-05-10
I altered a table and lost all the data on that table , is there anyway to undo this?
I have a back up on 9 aug 2010 , too old . i cant loose the data from that date till today
Hope anyone can help
USE [TFI]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoices__Dates__6319B466]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoices] DROP CONSTRAINT [DF__Invoices__Dates__6319B466]
END

GO

USE [TFI]
GO

/****** Object:  Table [dbo].[Invoices]    Script Date: 11/10/2010 22:27:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoices]') AND type in (N'U'))
DROP TABLE [dbo].[Invoices]
GO

USE [TFI]
GO

/****** Object:  Table [dbo].[Invoices]    Script Date: 11/10/2010 22:27:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Invoices](
	[Dates] [smalldatetime] NULL,
	[Invoice] [numeric](18, 0) NOT NULL,
	[Locator] [char](6) NOT NULL,
	[Agency] [char](3) NOT NULL,
	[Agent] [char](2) NOT NULL,
	[Net] [money] NOT NULL,
	[Sell] [money] NOT NULL,
	[Pax] [char](1) NOT NULL,
	[CRS] [char](1) NULL,
	[FareType] [char](1) NULL,
	[Airline] [char](2) NULL,
	[DepCity] [char](3) NULL,
	[ArrCity] [char](3) NULL,
	[FirstName] [char](30) NULL,
	[LastName] [char](30) NULL,
	[DepDate] [char](10) NULL,
	[Email] [char](200) NULL,
	[Ship] [char](2) NULL,
	[Status] [char](15) NOT NULL,
	[Comn] [money] NULL,
	[TAcomn] [money] NULL,
	[Delivery] [money] NULL,
	[Fee] [money] NULL,
	[Ccharge] [money] NULL,
	[TAgent] [char](20) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Invoices] ADD  DEFAULT (getdate()) FOR [Dates]
GO

Open in new window

0
Comment
Question by:Nabilbahr
  • 6
  • 4
11 Comments
 
LVL 80

Expert Comment

by:arnold
ID: 34108823
If you have your database in full recovery mode, run the transaction log backup and then you can replay the transaction log with the restoration of the full prior backup to get the database restored to the time prior to the execution of the alter directive.
If your database is in Simple mode, you are out of luck.
0
 

Author Comment

by:Nabilbahr
ID: 34108855
Great   rrecovery model is full , what is the steps i should take now
0
 
LVL 80

Accepted Solution

by:
arnold earned 2000 total points
ID: 34108909
you must have a huge Log file LDB if this is an active database.
issue a backup for the transaction log.

NOTE:     Make sure you do not backup the database or you will lock in the changes.


right click on the database, under tasks select backup, under what you want to backup select the transaction log.
Using the graphical SSMS right click on the database and select tasks\back up
under the backup type, select the transaction log.

http://technet.microsoft.com/en-us/library/ms179478.aspx

It includes links on how to restore database using the transaction logs as one of the links.

The one of interest to you for the restore is:
http://technet.microsoft.com/en-us/library/ms190982.aspx

If you've not tested the restore of the prior database, you should backup the current one to avoid losing more data.

If you have the option to try the restore on a different system without making the existing setup worse, I'd suggest you do that when it is successful, you might need only copy the data from the invoice table only.


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Nabilbahr
ID: 34108910
now i'm trying to run the transaction log backup .  would that be a new file that i save somewhere?

how to replay the transaction log with the restoration of a full prior back up?

I'm confused.  does that mean I need the last back up file (.bak file)  in this process?
0
 
LVL 80

Expert Comment

by:arnold
ID: 34108916
FYI, when altering table schema while trying to maintaining data, never issue a Drop table!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 34108925
since your backup is very old , you may need to use some theird party tools like
http://www.red-gate.com/products/SQL_Log_Rescue/
http://www.apexsql.com/sql_tools_log.aspx 
0
 
LVL 80

Expert Comment

by:arnold
ID: 34108932
You would need to bring the database to the point in time where the data is in.
You would need to first restore the full backup and leave it in recovery mode.
You would then use the transaction log (.trn) file from your recent backup to bring the database to the state in a point in time prior to your issuing the alter database scheme.
and here you will bring it back online at the conclusion of the restore.

I've not tried it, but do not believe you can use the transaction log to roll back the database to the state prior to the transaction.


0
 
LVL 80

Expert Comment

by:arnold
ID: 34109011
once you are done with this, make sure to setup a maintenance plan/sql jobs that will regularly backup your database and transaction logs such that the restore/recovery will be quicker with a significant reduction in the possibility of massive data loss.
0
 

Author Comment

by:Nabilbahr
ID: 34109124
now i cannot restore because of this error
"my database is in use by this session. it is recommended that the master database be used when performing this operation"

I set the default database to master for all logins  but still the same error
0
 
LVL 80

Expert Comment

by:arnold
ID: 34109182
Try restoring to a new database name.

Note that to restore you have to terminate any other sessions.
You can change the database to single user.

You may have an open query window for this database and this is the error.
i.e. you have SSMS open and you have one of the windows reflecting this database for the query window.  If you have many windows or do not want to take the time looking. exit out of the SSMS and start it a new. or close all tabs in ssms such that you can only see the object explorer tab and the database listing pane.  

0
 

Author Comment

by:Nabilbahr
ID: 34115234
the problem is solved
using the master db as default restarting the SQL services helped the backup
thanks all
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question