Big Problem with Data Deletion 1

[roccorocks]

Ok, the strangest thing has happened to my DB. I changed one of the fields on a table to allow null values. For some reasons it shows my create date as when I saved that value. What the heck is going on here!!! My 10 million plus records are gone. I can do a backup to get the info back, but why has this happened? Please help!

Tim R

 

[JayKusch]

Funny question but are you sure they are gone. Do a refresh on the session in Enterprise Manager just to be sure it is current. Check the properties of the table and then reopen the table up. I would think that to delete 10 million records your TLog would be quite large and it should have taken some time to delete all those records, which are two events that you should have noticed if in deed the records are gone. But ... stranger things have happened.

Thanks

J. Kusch

 

[JayKusch]

As for the comment of "For some reasons it shows my create date as when I saved that value" ... that is the default occurance when you tweak a field in your tables.

Thanks

J. Kusch

 

[roccorocks]

Jay, thanks for the quick response. I tried refreshing and closing the enterprise manager, that had no effect on the records not being there. Sorry about the create date issue, I forgot thats how SQL Server works. I have a valid backup. I use the backup in SQL Server, is there any way to just pull one table? Or does it have to be the full DB and then the Transaction log to point of recovery (won't the transaction log have the current problem table in it?). Also, if I just use the valid full backup from this morning, do i have to kick all users out of the system?

Tim R

 

[JayKusch]

OK ...

I tried refreshing and closing the enterprise manager, that had no effect on the records not being there.

-- Damn ... well ya never know until you try.

is there any way to just pull one table?[b/]

-- NOPE ... not yet at least
(won't the transaction log have the current problem table in it?)

-- Do a restore on the tlog to a Point In Time. BooksOnLine will give you the syntax you need. Just restore it to the point in time "prior" to your table tweak.

do i have to kick all users out of the system?

YES

Thanks

J. Kusch

 

[roccorocks]

Hmmm, how about this Jay. Can I restore my backup to different database? You know how SQL Server allows a restore as, and then select the original DB backup? That might be the easiest way for me to go (restore to diff database and pull info from table accross). Is that doable?

Thanks for all your help,

Tim R

 

[SQLBill]

Have you tried this in Query Analyzer:

SELECT COUNT(*) FROM table_name

Just to make sure there isn't any data? That will also eliminate a possible problem with Enterprise Manager and not the database itself.

-SQLBill

 

[roccorocks]

WHOA! Ok, something really strange is going on. Jay, I think you were right about the info still being there. But it is wierd, when i double click on the table (in enterprise manager) I see the small table size. I did a query to get the count and the full data is still there!? Any thoughts Jay?

thanks,

Tim R

 

[roccorocks]

Thanks for the tip Bill, I beat you to it! LOL. I should have done that before I even posted. Have you ever seen something like this Bill?

Sorry to waste your time Jay, for that I give you a Star.

thanks,

Tim R

 

[JayKusch]

Yes ... that would be the best approach is you have the room for the extra database being created.

Also ... Try Bill's suggestion. I just do not see how you could loose 10 million records by changing a tables layout.

You may have been caught in one of SQL Server famous "fire hose" modes and may not be able to see the data until all the records in your table were adjusted to accept a NULL in a predefined column.

Thanks

J. Kusch

 

[JayKusch]

Since that was the case .... 10 to 1 I'll bet on the comment I made above ... You may have been caught in one of SQL Server famous "fire hose" modes

Thanks

J. Kusch

 

[MDXer]

To better understand what is happening here is the code generated by Enterprose Manager when you set an existing column to allow nulls.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_NumberEncryption
	(
	Card_number numeric(18, 0) NULL,
	encrypted_number varbinary(200) NULL
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.NumberEncryption)
	 EXEC('INSERT INTO dbo.Tmp_NumberEncryption (Card_number, encrypted_number)
		SELECT Card_number, encrypted_number FROM dbo.NumberEncryption TABLOCKX')
GO
DROP TABLE dbo.NumberEncryption
GO
EXECUTE sp_rename N'dbo.Tmp_NumberEncryption', N'NumberEncryption', 'OBJECT'
GO
CREATE CLUSTERED INDEX NUMEx ON dbo.NumberEncryption
	(
	Card_number
	) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
COMMIT

You can see that you aren't just adding the NULL but you are creating and populating the table. Which explains the create date.

Enterprise manager is the least efficient way to do this. Most Table changes in enterprise manager result in this type of change which can be a total pain and disatorous in some instances. The only change I make in EM is adding a Column when it must be placed in a Set order other wise I use Alter Table statements.

Alter Table NumberEncryption Alter Column encrypted_number varbinary(200) Null

Has the same end result without all the over head.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[roccorocks]

Thanks for the tip MDXER, I will be sure to use that next time I need to alter a table.

Thanks,

Tim R