can anyone please point me in the right direction as to how to go about rebuilding the transaction log files on a database?
here is the scenario:
1 - transaction log drive failed and transaction log file is basically gone.
2 - database file is fine.
3 - backup is too old or for all intents and purposes non-existent.
the server initially showed the database as suspect. the database was detached and an attempt was made to attach it with a recovered copy of the transaction log file but apparently it was too corrupted and the server didn't like it.
any suggestions would be greatly appreciated.
by the way, after looking at some of the posts here, i tried ApexSQL Log and Red-Gate Rescue bundle but these tools seem to require a database to at least show up on the database list, even if it is suspect.
the database doesn't even show up on the list since it was detached.
thanks in advance.
For SQL 2005, you could try attaching using CREATE DATABASE with the ATTACH_REBUILD_LOG option. This will only work if the data file was shut down cleanly, though.
If this does not work, you might try creating a new database, setting it OFFLINE with ALTER DATABASE, deleting the data and log file, copy the data file from your old database to be the one from the new database, set the database ONLINE with ALTER DATABASE (which will likely fail), and then set the database to EMERGENCY with ALTER DATABASE. Then you can look at the "DBCC Operation in Emergency Mode" section in the topic "DBCC (Transact-SQL)" to try to salvage the database.
|||
have u tried sp_attach_single_file_db
Madhu
|||this is a SQL 2000 box.
would this still work?
|||I hadn't tried what you suggest.
i looked it up in the help and it seems pretty clear that the database has to be detached in just the right way and at least one transaction log file needs to be available.
but, following your suggestion i tried it anyway and the response was basically the same as trying to mount the database in the usual way. The response to the attempt was: Could not open new database 'Lab_Config'. CREATE DATABASE is aborted. Device activation error. The physical file name 'L:\SqlLogs\Lab_Config_log.LDF' may be incorrect.
Drive L: is the destroyed drive.
|||here is a rundown of what i did. maybe it is not correct or you have a suggestion on how this procedure can better be used to resolve this issue.
the statement used was taken from the help section for this stored procedure and it is as follows:
EXEC sp_attach_single_file_db @.dbname = 'LabConfig',
@.physname = 'E:\DataBases\Data\Lab_Config.mdf'
the response was:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'LabConfig'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'L:\SqlLogs\\Lab_Config_log.LDF' may be incorrect.
Please note the double reverse slash in the path in the response! does that look right?
i have several databases on this server that are without transaction logs. i tried the above stored procedure on all of them and the double reverse slash was present in all the responses.
by the way, drive L: has been replaced and it is now functional. the directory in SqlLogs exists and is configured with Full Access for Everyone.
|||there should not be any log in the L drive while u run this command. make sure that 'L:\SqlLogs\\Lab_Config_log.LDF' if exists in L drive cut & paste to some other location and try the same. And also it reminds me that, this command only works with singel log and datafile database.
Madhu
|||the L:\sqllogs directory is empty.
and all the databases in this condition are single log and datafile databases.
|||You're going to have to use undocumented commands to get around this - please contact Product Support who will be able to help you use them correctly.
Thanks
