SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 How I Rescue Data from Corrupted Databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22415 Posts

Posted - 03/07/2007 :  06:10:54  Show Profile  Reply with Quote
Edit: Some changes added

This is my procedure for "rescuing" data from a corrupted database. Obviously restoring from backup is a lot easier!

0) Set the damaged database to Read-Only. if you don't have a backup make one now.

1) Script the database

2a) Create a new, TEMP database - preferably on a different machine in case of hardware problems on the original machine

2b) Size the Data for the TEMP database same size as the original (to avoid dynamic extensions). Size the Log something large-ish!

3) Run the Script on the TEMP database. Do NOT create any FK etc. yet

4a) Attempt to transfer all tables:

-- Prepare script of: INSERT INTO ... SELECT * FROM ...
SET NOCOUNT ON
SELECT 'PRINT ''' + name + '''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
	+ CASE WHEN C.id IS NULL
		THEN '' 
		ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] ON' + CHAR(13) + CHAR(10)
		END
	+ 'INSERT INTO MyTempDatabase.dbo.[' + name + ']' + CHAR(13) + CHAR(10) 
	+ 'SELECT * FROM dbo.[' + name + ']' + CHAR(13) + CHAR(10)
	+ CASE WHEN C.id IS NULL
		THEN ''
		ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] OFF' + CHAR(13) + CHAR(10)
		END
	+ 'GO' 
FROM	dbo.sysobjects AS O
	LEFT OUTER JOIN
	(
		SELECT DISTINCT C.id
		FROM	dbo.syscolumns AS C
		WHERE	C.colstat = 1		-- Identity column
	) AS C
		ON C.id = O.id
WHERE	    type = 'U' 
	AND name NOT IN ('dtproperties')
ORDER BY name
SET NOCOUNT OFF

this generates statements like this:

PRINT 'MyTable'
GO
SET IDENTITY_INSERT dbo.[MyTable] ON
INSERT INTO RESTORE_XFER_TEMP.dbo.[MyTable]
SELECT * FROM dbo.[MyTable]
SET IDENTITY_INSERT dbo.[MyTable] OFF
GO

4b) This will give some sort of error on the tables which cannot be copied, and they will need to be rescued by some other means.

5a) Each "broken" table needs to be rescued using an index. Ideally you will have a clustered index on the PK and that will be undamaged, so you can "rescue" all the PKs into a temp table:

-- Copy PK fields to a temporary table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_PK
-- TRUNCATE TABLE MyRestoreDatabase.dbo.MyBrokenTable
SELECT	[ID]=IDENTITY(int, 1, 1), 
	[IsCopied]=CONVERT(tinyint, 0), 
	MyPK
INTO	MyRestoreDatabase.dbo.TEMP_RESCUE_PK 
FROM	MyBrokenDatabase.dbo.MyBrokenTable 
ORDER BY MyPK

5b) If that is successful you have a list of all the PKs, so can can try to copy data matching those PKs, in batches:

-- If OK then selectively copy data across
-- First Prep. a temp Batch table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
SELECT TOP 1 [ID]=CONVERT(int, NULL), [IsCopied]=CONVERT(bit, 0), MyPK
INTO	MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
GO
--
DECLARE	@intStart	int,
	@intStop	int,
	@intBatchSize	int

-- NOTE: After the first run set these to any "gaps" in the table that you want to fill
SELECT
	@intStart = 1,
	@intBatchSize = 10000,
	@intStop = (SELECT MAX([ID]) FROM MyRestoreDatabase.dbo.TEMP_RESCUE_PK)

SELECT	@intStart = MIN([ID])
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE	    IsCopied = 0
	AND [ID] >= @intStart

WHILE	@intStart < @intStop
BEGIN
	SET ROWCOUNT @intBatchSize

	-- Isolate batch of Keys into separate table
	TRUNCATE TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
	INSERT INTO MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
	SELECT	T.*
	FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
	WHERE	    IsCopied = 0
		AND [ID] >= @intStart 
		AND [ID]  < @intStart + @intBatchSize

	-- Attempt to copy matching records, for this batch
	PRINT CONVERT(varchar(20), @intStart)
	INSERT INTO MyRestoreDatabase.dbo.MyBrokenTable
	SELECT	S.*
	FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH AS T
		LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS D
			ON D.MyPK = T.MyPK
		-- This will try to get the data from the broken table, it may fail!
		JOIN MyBrokenDatabase.dbo.MyBrokenTable AS S
			ON S.MyPK = T.MyPK
	WHERE	D.MyPK IS NULL	-- Belt and braces so as not to copy existing rows

	-- Flag the rows just "Copied"
	UPDATE	U
	SET	IsCopied = 1
	FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK AS U
	WHERE	IsCopied = 0
		AND [ID] >= @intStart 
		AND [ID]  < @intStart + @intBatchSize

	-- Loop round, until done
	SELECT	@intStart = @intStart + @intBatchSize
END
GO
SET ROWCOUNT 0	-- Turn OFF!!
GO

5c) This will copy in batches of 10,000 [you can adjust @intbatchSize depending on table size] until it gets to a damaged part of the table, then it will abort.

Change the @intStart to the last ID number displayed, and reduce @intBatchSize (by an order of magnitude each time) until you have rescued as many records as possible in the first "part" of the table.

5d) Reset the batch size @intBatchSize to 10,000 [or whatever size is appropriate], and increase the @intStart repeatedly until you are past the damaged section - copying will start again, and will abort if there are further damaged sections

5e) Repeat that process until you have rescued as much of the data as is possible

6) Check what is left to be rescued

-- Check amount NOT done:
SELECT	COUNT(*), MIN([ID]), MAX([ID])
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE	    IsCopied = 0
--	AND [ID] > 123456	-- Optionally count items after a "gap"
--
-- Double check that IsCopied set correctly, and the number of records "lost"
SELECT	COUNT(*), 
	[IsCopied] = SUM(CONVERT(int, IsCopied)), 
	[IsCopied+Record] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
	[IsCopiedNoRecord] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NULL THEN 1 ELSE 0 END),
	[IsNOTCopied] = SUM(CASE WHEN IsCopied = 0THEN 1 ELSE 0 END),
	[IsNOTCopied+Record] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
	[IsNOTCopiedNoRecord] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NULL THEN 1 ELSE 0 END)
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
	LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS C
		ON C.MyPK = T.MyPK
--
-- List of "Lost" records
SELECT	MyPK
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE	    IsCopied = 0
ORDER BY [ID]

You will then have to "find" and rescue the lost records somewhere.

I have a further process using OPENQUERY() to rescue records to fill the gaps in the event that they are available on a remote system - a straight JOIN to get them is going to be far to slow on anything other than tiny tables!

7a) Create the FKs etc. Update the statistics, rebuild indexes, and possibly shrink the Log if it is unrealistically big
7b) Backup and Restore over the original database
7c) DBCC CHECKDB ('MyDatabaseName') WITH ALL_ERRORMSGS, NO_INFOMSGS

Good luck!

Kristen

Edited by - Kristen on 09/24/2007 04:32:30

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 03/08/2007 :  03:23:13  Show Profile  Reply with Quote
Very nice!

How often do you experience corrupted data? I've yet to experience it, and I've been working with SQL Server since 1997 (not on a very large scale though).

/Andraax

Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 03/08/2007 :  03:38:53  Show Profile  Reply with Quote
We've had three corruptions on one server in the last year. All caused by failure of a drive in RAID5.

That database is in FULL recovery mode and on all three occasions we were able to fix it by:

Take a "final" TLog backup
Restore FULL backup
Restore all TLog backups

thus there was ZERO data loss. We keep the Backups on separate drives to the data - so the chance of them both failing at the same time is very low. Plus that server should be using RAID10 ... but that's another story!

All the corruptions I have come across were caused by hardware failure.

Kristen

Edited by - Kristen on 03/08/2007 03:41:40
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/20/2007 :  04:44:49  Show Profile  Reply with Quote
There are some useful steps to Diagnose and Recover a corruption here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84958#314526
Go to Top of Page

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 08/03/2012 :  01:20:51  Show Profile  Reply with Quote
Hi Frank,

This is a lovely post.

I have little requirement. In your script, it is able to handle both non-identity tables as well as identity tables.

The problem here is , if we do not specify the explicit column list for identity tables, the insertion fails with below error.
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Target.dbo.Emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.


Do you have any logic or script to modify the same above script to handle this.

Here is what i have tested.

Src
=====
use master
go
CREATE DATABASE Demo
go

use demo
go

create table Dept
(
Deptno int not null primary key,
Dname varchar(100)
)
go
insert into dept values(10,'ACCOUNTS');
insert into dept values(20,'SALES');
insert into dept values(30,'MARKETTING');
insert into dept values(40,'FINANCE');
go

create table Emp
(Empno int not null primary key identity(1,1),
Ename varchar(10),
Esal int,
Deptno int references dept(deptno)
)
go
insert into Emp(Ename,Esal,Deptno) values('Manu',9000,10)
insert into Emp(Ename,Esal,Deptno) values('Anu',9000,20)
insert into Emp(Ename,Esal,Deptno) values('Ajay',9000,30)
insert into Emp(Ename,Esal,Deptno) values('Sidhu',9000,40)
insert into Emp(Ename,Esal,Deptno) values('Brahma',9000,20)
insert into Emp(Ename,Esal,Deptno) values('Mayank',9000,10)

create table Student
(sid int,
sname varchar(10)
)
go

insert into student values(1001,'Mark')
insert into student values(1002,'Anthony')
insert into student values(1003,'Smith')
go

Target
======
use master
go
CREATE DATABASE target
go

use target
go

create table Dept
(
Deptno int not null primary key,
Dname varchar(100)
)
go

create table Emp
(Empno int not null primary key identity(1,1),
Ename varchar(10),
Esal int,
Deptno int references dept(deptno)
)
go

create table Student
(sid int,
sname varchar(10)
)
go


use Demo
go

-- Prepare script of: INSERT INTO ... SELECT * FROM ...
SET NOCOUNT ON
SELECT 'PRINT ''' + name + '''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+ CASE WHEN C.id IS NULL
THEN ''
ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] ON' + CHAR(13) + CHAR(10)
END
+ 'INSERT INTO Target.dbo.[' + name + ']' + CHAR(13) + CHAR(10)
+ 'SELECT * FROM dbo.[' + name + ']' + CHAR(13) + CHAR(10)
+ CASE WHEN C.id IS NULL
THEN ''
ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] OFF' + CHAR(13) + CHAR(10)
END
+ 'GO'
FROM dbo.sysobjects AS O
LEFT OUTER JOIN
(
SELECT DISTINCT C.id
FROM dbo.syscolumns AS C
WHERE C.colstat = 1 -- Identity column
) AS C
ON C.id = O.id
WHERE type = 'U'
AND name NOT IN ('dtproperties')
ORDER BY name
SET NOCOUNT OFF


-- output -- This will take care of identity tables as well and normal tables


PRINT 'Dept'
GO
INSERT INTO Target.dbo.[Dept]
SELECT * FROM dbo.[Dept]
GO
PRINT 'Emp'
GO
SET IDENTITY_INSERT Target.dbo.[Emp] ON
go
INSERT INTO Target.dbo.[Emp]
SELECT * FROM dbo.[Emp]
SET IDENTITY_INSERT Target.dbo.[Emp] OFF
GO
PRINT 'Student'
GO
INSERT INTO Target.dbo.[Student]
SELECT * FROM dbo.[Student]
GO

Appreciate if someone can provide inputs on this.

Thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000