August 10, 2007 at 6:51 am
The drive holding the logs was full w/ 60 gigs of logs.
I stopped the db and deleted the log files. Now i'm hosed Please help. No drive restore avail.
I still have the mdf files. I tried to detatch the db but can't due to log on error, tried to delete the db after moving the mdf file, unable due to log on error.
In a perfect world i would delete the db. Create a new one w/ the same privs and then replace the newly created mdf file.
Thoughts?
Please help
when I try to delete i get the following.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Failed to connect to server sql. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot open user default database. Login failed.
Login failed for user 'path\user'. (Microsoft SQL Server, Error: 4064)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
August 10, 2007 at 7:14 am
Are you saying that you have no form of backup on anything of any kind?
No database backups, no file-to-tape backups, no nothing?
If that's the case, then I think that the only (however small) chance still exists, is to try to recover those deleted logs from that drive with some 'undelete-utlity' or similar.
/Kenneth
August 10, 2007 at 8:41 am
I tried to simulate your issue, however when I start the server again the database is listed suspect and I can simply detach and re-attach the database and I'm given the option to create a new log file. What type of login error are you recieving? If it's something related to other connection to the DB you could try doing the detach and re-attach in single user mode (sqlservr -m) from the command prompt.
August 10, 2007 at 11:37 am
Solved for now. I'll post with a solution when i get some time.
Thanks everyone for your help.
August 10, 2007 at 12:24 pm
Ya, I'd like to hear this one!
August 10, 2007 at 9:21 pm
refer, this http://deepakinsql.blogspot.com/2007/08/how-to-recover-from-suspect-database.html check if this helps
[font="Verdana"]- Deepak[/font]
August 14, 2007 at 7:40 am
I know this is solved, but for future reference and for other readers, if you have an MDF you're largely okay. Any pending transactions would be gone and you'd lose the ability to restore to a point in time, but everything else should be safe.
Getting the DB re-attached nicely is the trick. Copy this to a SSMS query window and hit Ctrl-Shift-m to replace the parameters with the name of your database and the full location and filename of the .mdf file. Run it and you're back in business.
create database <db name?, sysname, >
on ( name = staging, filename = '<path & filename?, varchar, >' )
for attach_rebuild_log
The only caveat is that it defaults to build the logfile on the same drive. If you have another disk available, it's a good practice to move the log file to another disk. You can accomplish this with ALTER DATABASE ... MODIFY FILE ...
See: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm example F.
Hope this helps,
Carter
But boss, why must the urgent always take precedence over the important?
August 14, 2007 at 8:16 am
I was able to accomplish the task using the method described in Deepak's entry. Sorry to be brief, thanks for your help everyone!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply