First of all I am not a seasoned DBA. Somehow I am made to do DBA work oflate unwillingly. So with a good working background of TSQL and little bit of DBA related commands I had to manage the crash situations. Most of the times I was able to get back the database by detaching, attaching the mdf files. In one situation I was out of my luck. The problems was the transaction log had grown too big. There was no space in the system to handle this.
I was very confident. My knowledge base said, take database offline. Detach. Delete the ldf file. Then finally attach the mdf file. This would create a ldf file again. So I went ahead. We had a database backup taken 2 days back. So first I tried to backup data. It was not doing. Space problem. Then tried backup log with no_log options. Not working. One of the message which came during the process was backup transaction log. Then tried to take database file offline and detach. Even this was not working. Still I tried 2/3 times and database was not visible in the Management studio. So I assumed that the process of detaching has happened properly. So when I tried to delete ldf file, it said the file is in use and cannot be deleted. So I simply stopped the sql service and deleted the ldf file. This started all the problem for me.
Thinking that everything is fine, I tried to attach the mdf file without ldf file. It was a shock. Its not working. Then I tried many ways. Created another database with same name. Tried to replace this mdf with actual mdf. All such efforts failed. FInally we were left with restoring the database from 2 days old backup file. Atleast we had that much of data. But 2 days transactions!! It was a tough time to get back this data. I had to get this somehow. Otherwise, a series of explanations.
Finally after searching quite a lot on the web, the following KB article from microsoft helped to get back the mdf in the Management studio.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1045929&SiteID=1
As given in this the database was brought to read-only, emergency mode. From this all the data was shifted to another database. This really helped me get back 2 days of lost data successfully.
Finally in the entire process, when I deleted the transaction log file, the sql server service had been stopped. But before that I was unable to detach the database. Probably someone can throw more light on this.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|