Secondary Data File Not Referenced SQL2K

Question

Post reply

Secondary Data File Not Referenced SQL2K

Simon Larsen

Ten Centuries

Points: 1013
More actions
January 26, 2009 at 7:19 pm

#217955

A client had a db in full recovery mode and wasn't backing up the log.
Disk filled up. They stopped SQL and deleted the log file.
Database has 1 mdf, 2 ndf and 1 ldf.
They have done a bunch of fiddling around before calling me.
The database now attaches with the new log file they created.
However the secondary data files are not present.
When specifying to attach the database with all files specified they are getting DB version errors.
The mdf does contain the schema correctly.
There are, of course, no recent backups (the dev guys did a weekend of loading data).
Is it possible to attach a secondary data file to a db? Even an unsupported method would probably do at the moment.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Have you tried using the ATTACH_REBUILD_LOG option with the CREATE DATABASE statement? Example would be as follows;

CREATE DATABASE YourDB ON

(FILENAME = 'c:\MSSQL\Data\YourDB.mdf'),

(FILENAME = 'c:\MSSQL\Data\YourDB_2.ndf')

FOR ATTACH_REBUILD_LOG;

GO

For this to work I believe that all transactions would have had to have been completed prior to the shutdown. I honestly haven't used it and when I just tried it on my local instance it failed due to an open transaction but it is at least worth a shot. Hopefully you will get better results.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Simon Larsen Ten Centuries Points: 1013 More actions · Answer 2

I believe attach rebuild is 2005+

The current log file created by the clients faffing around actually attaches fine.

Smells to me like they have managed to redo the mdf so that it is no longer aware of the ndf's. Not sure how that would be done but feels that way.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Do they have the database attached? If not, and the secondary files are missing, are they in the primary filegroup or a secondary filegroup? If in the primary filegroup then its game over. If in the secondary filegroup, you *might* be able to get the database to attach - but its unlikely.

What are the exact errors they're seeing?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Simon Larsen Ten Centuries Points: 1013 More actions · Answer 4

Yes the database is attached currently.

The current error is when you do a select from any table in the second or third data file it errors. (Sorry I don't have the exact message, they have just upgraded their vpn to use checkpoint which I don't have installed so can't jump on to check).

The secondary files exist in the file system but do not show in the properties of the database.

From memory there are secondary and tertiary file groups, they just have no files in them.

A basic sp_attach_db with the single mdf works fine to attach the db and their recreated log file (they used dbcc rebuild_log).

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

ok - so somehow in the sequence of what they did, they've disconnected the files from the database - a guess would be that a transaction that was involved in creating the extra files or moving tables between them was lost when they rebuilt the log.

You could try duplicating the database and creating the secondary filegroups again, creating empty tables in the filegroups, then shutting down and swapping in the old files with the table data in. That's a long-shot, and difficult to do but there's a small chance it may work. Warning - if you're going to attempt this, do it with a completely separate copy of the database.

To be honest, my solution above is so hard to get right, I advise you to just tell them that they've lost the data and they need to chalk it up to experience - not what they're going to want to hear.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Simon Larsen Ten Centuries Points: 1013 More actions · Answer 6

Thanks for that. They did end up giving up on the recovery and went to a couple of weeks old backup. There are even rumours that the backup plan I proposed some years back might be implemented now.