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
 Data Corruption Issues
 Tail Backup of Offline SQL2000 Database?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  10:57:58  Show Profile  Reply with Quote
Server has failed. Files have been rescued from a virtual disk copy. Only MDF and LDF available as SQL Agent has not run for months and noone noticed, and thus no recent BAK files.

First database attached OK. DBCC full of errors. I made a TLog backup (which was huge of course), restored last FULL and all Tlog backups since, including the Tail Log Backup, and DBCC is OK.

However, I have another database that won't ATTACH. (causes exception error files to be produced).

I have restored its last Backup (to create a database), Taken that database offline, replaced the files with copied of the rescued MDF / LDF. Cannot bring online (presumably the same problem as using ATTACH).

Is there any way I can get a Tail log Backup of this database? (SQL2005+ would allow Tail Log Backup of OFFLINE database I think?)

Command I am trying is:

BACKUP LOG MyDatabase
	TO DISK = 'D:\DATA\MSSQL\BACKUP\MyDatabase.TRN'
WITH 
	DESCRIPTION = 'MyDatabase TailLog'
	, INIT
	, STATS = 10
	, NORECOVERY

"Server: Msg 942, Level 14, State 4, Line 2
Database 'MyDatabase' cannot be opened because it is offline.
Server: Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
"

Any other routes to getting it mounted?

@@VERSION =

Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/20/2011 :  11:09:51  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
>> SQL Agent has not run for months and noone noticed
Who's going to be first to say it?

You could set the database to emergency mode and copy the data out.
Do you have access to all the database files?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/20/2011 :  11:10:55  Show Profile  Visit russell's Homepage  Reply with Quote
I might be mistaken, but 99% sure that you can't do it in SQL 2000.

By the way, when you swapped out the files, were they the exact same size? If not, the attach wouldn't work anyway.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  11:20:47  Show Profile  Reply with Quote
quote:
Originally posted by nigelrivett

>> SQL Agent has not run for months and noone noticed
Who's going to be first to say it?



Not a system I'm responsible for, trying to help a client out as I probably know more about SQL than their IT support people do ...

But yeah, the other thoughts that are in your mind will be spot on!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  11:22:53  Show Profile  Reply with Quote
quote:
Originally posted by russell

By the way, when you swapped out the files, were they the exact same size? If not, the attach wouldn't work anyway.


No, I'm sure they weren't - TLog will have been growing for 6 months since the last backup - if nothing else.

So ...

restore the very old backup
extend it to match the actual size of rescued MDF and LDF files
Put the database offline
Replace the files with the rescued ones
Try bringing it online

Will that do?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  11:25:35  Show Profile  Reply with Quote
quote:
Originally posted by nigelrivett

You could set the database to emergency mode and copy the data out.
Do you have access to all the database files?



Just want to be sure I've understood Nigel:

I have the MDF and LDF that were rescued (by a data recovery company)

I have a virtual disk copy that was taken in December (for a different purpose). No recent BAK files on that, but SQL was shut down cleanly at the time, so the MDF / LDF from that are probably OK

Emergency mode will let me try to copy data out from an MDF / LDF that won't mount? (if so I could do with a pointer to an article if you know of one please)
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/20/2011 :  11:25:51  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Was at a place recently where they had a lot of processing in an ssis package. The last step was to take backups of all databases.
Unfortunately there was a failure near the begining so no backups were being taken for a couple of months.

There comment was that the system was still working so it wasn't considered important.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/20/2011 :  11:27:34  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
http://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.html


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/20/2011 :  11:31:54  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by Kristen

quote:
Originally posted by russell

By the way, when you swapped out the files, were they the exact same size? If not, the attach wouldn't work anyway.


No, I'm sure they weren't - TLog will have been growing for 6 months since the last backup - if nothing else.

So ...

restore the very old backup
extend it to match the actual size of rescued MDF and LDF files
Put the database offline
Replace the files with the rescued ones
Try bringing it online

Will that do?


Or just create the database with the files at the prescribed sizes.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  11:33:03  Show Profile  Reply with Quote
Thanks Nigel, your article is clear and I'll give it a go later on.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  11:42:51  Show Profile  Reply with Quote
Any chance that and Offline Tail Log Backup will work on SQL2008 (if I do the file-replace trick)?

The database will obviously be an earlier, not-migrated, version.

1) Might the TLog backup file be restorable under SQL2000? (Seems unlikely!)
2) If I can get the database restored under SQL2008 and I can migrate the data backwards to SQL2000
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/20/2011 :  11:51:01  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I would be surprised.
I would expect it to spot that it's a wrong file format and then try to migrate or just refuse to access it.

No harm in trying on a file copy though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  12:20:00  Show Profile  Reply with Quote
quote:
Originally posted by russell
Or just create the database with the files at the prescribed sizes.


I can't get the exact same sizes

MDF is OK.

Actual size of LDF is 261752K

LOG ON
( NAME = 'MyDatabase_Log',
   FILENAME = 'D:\DATA\MSSQL\LOG\MyDatabase_Log.LDF',
   SIZE = 261752KB,
   FILEGROWTH = 1MB )

Actual file size is 261,752
Size created is 261,760

Reducing the SIZE downwards until I get something smaller than 261,760 is quite a bit smaller, so I left it as the slightly larger size.

I tried FILEGROWTH = 1KB and that made no difference.

Perhaps its only the MDF size that has to be exactly correct.

After the stop / copy files over / Restart its come up as SUSPECT, so hopefully that is OK
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  12:22:28  Show Profile  Reply with Quote
Ah ... Nigels article says:

Create a database with an mdf of the same size as the old one
stop the server.
Copy the corrupt mdf over the newly created one.

so maybe that's OK

(But I copied the LDF too - still hoping I can get a Tail Log Backup)

Edited by - Kristen on 01/20/2011 12:22:54
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  12:37:30  Show Profile  Reply with Quote
With the database in SUSPECT (rather than OFFLINE) it lets me back it up. Looks like the LDF is corrupted though?

BACKUP LOG MyDatabase
	TO DISK = 'D:\DATA\MSSQL\BACKUP\MyDatabase_TailLog.TRN'
WITH 
	DESCRIPTION = 'MyDatabase TailLog'
	, INIT
	, STATS = 10
	, NO_TRUNCATE

"10 percent backed up.
20 percent backed up.
30 percent backed up.
40 percent backed up.
Server: Msg 3241, Level 16, State 40, Line 1
The media family on device 'D:\DATA\MSSQL\BACKUP\MyDatabase_TailLog.TRN' is incorrectly formed. SQL Server cannot process this media family.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
"

I'd be happy with the first 40% if there was a way to get that only?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2011 :  18:41:24  Show Profile  Reply with Quote
I SET status = 32768 then tried deleting the LDF file and creating a fresh one with:

DBCC rebuild_log('MyDatabase', 'D:\DATA\MSSQL\LOG\MyDatabase_Log.LDF')
GO

then

DBCC CHECKDB ('MyDatabase') WITH ALL_ERRORMSGS, NO_INFOMSGS
go

gave me

Server: Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 341576255, index ID 3. Page (1:16714) is missing a reference from previous page (1:16801). Possible chain linkage problem.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 341576255, index ID 3: Page (1:16801) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 341576255, index ID 3, page (1:16801). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 341576255, index ID 3. Page (1:16801) was not seen in the scan although its parent (1:6866) and previous (1:18304) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'DRAWING' (object ID 341576255).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'MyDatabase'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyDatabase ).

SELECT	[TableName] = object_name(id),
	[IndexName] = name
FROM	sysindexes 
WHERE	    id = 341576255
	AND indid = 3

gives me

DRAWING IDXDRAWINGCD

can I just drop and re-create that index? or is there more to it than that?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/20/2011 :  22:34:59  Show Profile  Visit russell's Homepage  Reply with Quote
If the ldf is no good, kinda defeats the whole purpose no?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2011 :  02:31:19  Show Profile  Reply with Quote
Well ... if the MDF can be used with no data loss that's fine we'll just carry on using that.

(Originally when I could not even mount it I was looking at ways of getting a tail log instead, to then recover from much earlier BAK file)

Someone else recovered the same database earlier on when they only had the MDF file (the data rescue people said it took ages to get the LDF back, which apparent implies greater chance of data damage) using a similar process:

http://www.tek-tips.com/faqs.cfm?fid=6260

and that database is now clean - I checked with them and they didn't do any sort of "Repair with lost data" as the DBCC CHECKDB was clean.

Not sure why my route has errors in the MDF though. Perhaps some junk has been rolled forward from the LDF file when it mounted?

(Nigel's instructions do say to only copy the MDF into place, I misread that and also copied the LDF, hence I'm thinking that may be the cause)

Edited by - Kristen on 01/21/2011 02:32:44
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/21/2011 :  13:19:05  Show Profile  Visit russell's Homepage  Reply with Quote
Kristen, where do you stand on this now?

Here's an excerpt from an article I wrote a couple years ago or so. It is about recovering a sql 2000 db without the tran log. I didn't follow Nigel's link, so probably much of the info is redundant.
quote:
Here are the steps to create a database from only an .Mdf:

1. Create a new database exactly the size of the old one. Log file size doesn’t matter, but the data file should be the same size precisely (see listing 1).
2. Swap your data file for the one in the newly created DB.
3. Update the status in sysdatabases.
4. Rebuild the log (see listing 2. Note the listing shows SQL 2000 syntax. See link at the end of this article for SQL 2005).
5. Perform consistency checks.
6. Set database options as desired.

If we are lucky, only user transactions were in the now unrecoverable log file and we have a few (or few hundred) bad records. Please see the article referenced above for the pitfalls of performing these actions. Again I’ll stress that this is only a last resort to recover data. Keep those backups ready! We are only as good as our last backup!

Listing 1:


USE master;
GO

CREATE DATABASE MyNewDatabase
ON
( NAME = MyNewDatabase_data,
FILENAME = 'D:\Data\MyNewDatabase_data.mdf', -- use your physical path
SIZE = 14976768KB, -- make exact size as .mdf u will swap in
FILEGROWTH = 5 )
LOG ON
( NAME = 'MyNewDatabase_log',
FILENAME = 'E:\logs\MyNewDatabase_log.LDF',
SIZE = 1024MB,
FILEGROWTH = 5MB )
GO

/*
NOW STOP SQL Server service
Delete MyNewDatabase_data.mdf at the OS level
Rename old .mdf to MyNewDatabase_data.mdf
START SQL Server service
MyNewDatabase will not be accessible yet
*/

Listing 2:

Use Master;
GO

-- allow updates to system tables
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

-- remove suspect flag, set single user
UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'MyDatabase';
GO

-- disable updates to system tables
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

/* RESTART SQL SERVER SERVICE */

-- rebuild the log file
DBCC REBUILD_LOG('MyDatabase','E:\MyDatabse_log.ldf')
-- change to your physical path
GO
-- Check db consistency
DBCC CHECKDB ('MyDatabase');
GO

-- Set DB Options
ALTER DATABASE MyDatabase SET MULTI_USER
GO


Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2011 :  14:46:54  Show Profile  Reply with Quote
"where do you stand on this now?"

I'm nervous!

I now have two (SQL 2000) databases. One which was recovered with just the MDF file, and had no DBCC CHECKDB errors, and the same one where both MDF and LDF were used (it wouldn't attach, so used Nigel's EMERGENCY MODE route), and the second one has errors in DBCC CHECKDB.

Client is using the first one, but I'm anxious that there may still be some issue in the database.

Data has NOT been exported and re-imported

I'll give your method a go and see if that gives me a different result.

Nigel had some variations on the value for [status] in

UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'MyDatabase';

and maybe the various things I tried upset something. Or maybe the fact that I included the LDF file (which was NOT in Nigel's instructions, my bad) caused something to be rolled forward - and that's what I am seeing in DBCC CHECKDB error list.
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.23 seconds. Powered By: Snitz Forums 2000