May 1, 2002 at 11:20 pm
How does one recover a database whose transaction log size inexplicably goes to 0. No backups are available.
The database is marked as suspect and offline.
May 2, 2002 at 4:43 am
First try sp_resetstatus to try and bring db back online. (I do suggest backup the db file by stopping SQL and make a copy beforehand then restart SQL which may come back then).
If that doesn't work try sp_detach_db to detach from server then rename the transaction log and try sp_attach_single_file_db.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 2:16 pm
You can also set the database status to Emergency mode which allows export of the data without the log file being present. This requires setting the system tables to allow updates, and changing the status of the suspect database to emergency mode (integer value = 32768) After this is done you can export the data to new database with an intact structure, drop the original and rename the new database to the original name. This is a complicated process that requires several specific options to be set in the export (i.e. transfer users and permissions) or the export will not yeild the desired results. It goes without saying that you should verify the contents of the new database before dropping the old.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply