October 27, 2016 at 4:53 pm
I have been asked to help out on a SQL2008 system which has been running with a corrupted log file for the msdb database for some time. I know the correct fix would be a restore from a known-good backup. Unfortunately in this case there isn't a backup for msdb from before the log file went bad.
So does anybody know of a good way to recover? Last time I had to do something like this was in the SQL2000 days when a DBCC REBUILD_LOG was an option. Can you do something like a DETACH, ATTACH_FORCE_REBUILD_LOG on msdb? Or am I going to have to do an uninstall / reinstall to get this fixed?
Note... Before anybody goes into scold mode, yes backups are important and would have easily fixed this. Sadly this system is not supported by our DBA team, we are just being asked for help by the application team that supports it now that it is broken.
October 28, 2016 at 2:39 am
I have never tried this on a system database, but in the documentation it is not mentioned it cannot be done.
FOR ATTACH_REBUILD_LOG
Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. There must be a <filespec> entry specifying the primary file. If one or more transaction log files are missing, the log file is rebuilt. The ATTACH_REBUILD_LOG automatically creates a new, 1 MB log file. This file is placed in the default log-file location. For information about this location, see View or Change the Default Locations for Data and Log Files (SQL Server Management Studio).
If the log files are available, the Database Engine uses those files instead of rebuilding the log files.
FOR ATTACH_REBUILD_LOG requires the following:
•A clean shutdown of the database.
•All data files (MDF and NDF) must be available.
This operation breaks the log backup chain. We recommend that a full database backup be performed after the operation is completed. For more information, see BACKUP (Transact-SQL).
Typically, FOR ATTACH_REBUILD_LOG is used when you copy a read/write database with a large log to another server where the copy will be used mostly, or only, for read operations, and therefore requires less log space than the original database.
FOR ATTACH_REBUILD_LOG cannot be specified on a database snapshot.
I have put some text in BOLD to emphasize things I would pay extra attention in above quote. Especially the quote about the usage of an existing file is important in your case. You need to rename or remove the existing file to prevent the ATTACH command from re-using the file.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply