Nabilbahr
asked on
undo sql alter command
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
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
ASKER
Great rrecovery model is full , what is the steps i should take now
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
FYI, when altering table schema while trying to maintaining data, never issue a Drop table!
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
http://www.red-gate.com/products/SQL_Log_Rescue/
http://www.apexsql.com/sql_tools_log.aspx
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.
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.
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.
ASKER
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
"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
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.
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.
ASKER
the problem is solved
using the master db as default restarting the SQL services helped the backup
thanks all
using the master db as default restarting the SQL services helped the backup
thanks all
If your database is in Simple mode, you are out of luck.