January 26, 2009 at 7:19 pm
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.
January 26, 2009 at 8:31 pm
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
January 26, 2009 at 10:00 pm
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.
January 27, 2009 at 1:20 am
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
January 27, 2009 at 2:01 am
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).
January 27, 2009 at 2:19 am
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
February 3, 2009 at 8:19 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply