May 16, 2010 at 4:46 pm
We have an situation where an external drive had been used in a test/development environment for database backups and some transactions logs including the transaction log for the tempdb.
The drive was accidentally knocked to the ground and destroyed. Now the SQL Server on the server will not start up. The master database data and transaction log files, as well as all the data files for all of the other user databases, are on a different drive that is working fine.
Foolishly some of the transaction logs were on the lost drive including the transaction log for tempdb.
At one point we were stretched for room for the data so I moved the transaction logs to the external drive with the backup files. There is space now on the new huge drive that is okay -- but I had not yet moved the transaction logs back [because of the other low resource -- TIME]
Is there a way to bring up the SQL Server from the command line [?] that would let me move the tempdb and salvage the data for the databases? Is it possible to retain the data if the transaction logs are lost?
We really would like to salvage a couple of these databases as we are in critical stages of development for a couple of projects.
Any direction will be greatly appreciated. I have not found any instruction for this type of recovery in the online documentation. Unfortunately we do not have backups of this drive.
May 16, 2010 at 4:54 pm
Since tempdb gets re-created each time SQL Server is started this shouldn't be a problem.
What's the error you are getting? is it that the failed drive is not longer there?
I would map a drive as the one that is now missing then start SQL Server, chances are a new tempdb will be created on the newly mapped drive.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 16, 2010 at 9:05 pm
tempdb is okay -- the files recreated when moved to an available drive, master and msdb are fine also.
The errors received now are for the user databases that had the log files on the lost drive. First message says it cannot find the file then there is an Error message that says the log cannot be rebuilt because the database was not cleanly shut down.
I can alter the database and modify the file to move the log file to a good location --- but I don't have the log files to physically move to the new location. The data files are fine. The backups are gone.
Is there a way to get around this problem? Is there a way to get a new log started with the database as it is?
May 16, 2010 at 10:36 pm
It's been a while since I've done this, so I don't know all the details, but it is theoretically possible to create a new empty log file and get the dtatabase to use that. You would need to do a bit of research on the net. In general though it has a 50/50 chance of working, it depends on the state of the database log and transactions at the time of the disaster.
Leo
Triving to provide a better service
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 17, 2010 at 1:07 am
The recommended approach would be to restore the user databases from backup.
Since you don't have backups, check this blog post for how to force SQL to rebuild the log of a DB that was no cleanly shut down. Do note, you may lose data here and it may not even work.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Once you have this problem resolved, put a proper backup strategy in place, one where every single backup cannot be lost if a single drive fails.
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
May 17, 2010 at 1:37 am
Why not use SSMS to detach then re-attach (remember to delete the log on the attach dialog) ? Or attach the .mdb to a new SQL instance?
I think that this meets your basic requirement - to get the database up and working. You will lose anything in the log, but that seems to have gone anyway.
May 17, 2010 at 2:16 am
ianT (5/17/2010)
Why not use SSMS to detach then re-attach (remember to delete the log on the attach dialog) ? Or attach the .mdb to a new SQL instance?
Because that won't work if the database was not shut down cleanly (the attach will fail), and the OP indicated indeed that the DBs were not shut down cleanly.
First message says it cannot find the file then there is an Error message that says the log cannot be rebuilt because the database was not cleanly shut down.
There's no need to detach/attach to recreate the log if the DB was cleanly shut down, SQL will do so as it opens the database, however it can only do that if the DB was cleanly shut down. If the DB was not cleanly shut down, it means that SQL needs to do restart-recovery on the database (rollback/rollforward) and, without the log, that's impossible. Hence the requirement to force the log rebuild and acknowledge that there may be data loss.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply