November 29, 2010 at 9:34 am
Hi all
I've got a db that is offline in a cluster during which our network team removed the LUN that contained the log file and backups. I only have a mdf file and the LUN has been reformatted to house other sql db's.
When i try to attach a single mdf or create new db with "for attach_rebuild_log" i get the same error:
CREATE DATABASE GL_AR_2009 ON
(FILENAME = N'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GL_AR_2009.mdf')
FOR ATTACH_REBUILD_LOG
GO
EXEC sp_attach_single_file_db @dbname='GL_AR_2009',
@physname=N'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GL_AR_2009.mdf'
GO
"The log cannot be rebuilt because the database was not cleanly shut down. (Microsoft SQL Server, Error: 5120)"
How can i bring it online?
November 29, 2010 at 10:42 am
Have you seen this :
http://www.sqlservercentral.com/articles/Disaster+Recovery+(DR)/63311/
not sure if it will work for you in your situation, but it's saved my bacon in the past.
November 29, 2010 at 10:43 am
You can hack it in. You will likely lose data, you may even end up with an unusable database.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
How come no backups? Someone not doing their job.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 11:16 am
Thank you.
I was able to recover needed data by creating a different db with same size and replacing the mdf file and than using emergency mode and running dbcc checkdb (repair_allow_data_loss).
Would you believe after all this stress our network team has the nerve to come back and tell me that they do have the LUN backed up on another SAN.
November 29, 2010 at 2:45 pm
AlexSQLForums (11/29/2010)
Thank you.I was able to recover needed data by creating a different db with same size and replacing the mdf file and than using emergency mode and running dbcc checkdb (repair_allow_data_loss).
Would you believe after all this stress our network team has the nerve to come back and tell me that they do have the LUN backed up on another SAN.
Unfortunately - that probably wouldn't do you any good either. SQL Server locks those files and backing up the files (e.g. copying) to another LUN wouldn't necessarily touch the SQL Server files. If it did, those files probably wouldn't be usable anyways.
Now, if they use some type of snap technology - that might work, but normally those tools don't work on the transaction log files.
What you really need to make sure happens is that you get an auxiliary copy of your backups. This can either be to tape or to another LUN (preferably on a different SAN). In fact, if you can set it up - having your backup drive presented from a different SAN than your data files would be ideal. That way, if something happens to your production SAN you should still have access to all of your backup files.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply