A system admin in a company I know of did exactly the same, "to reclaim disk space". His backup routine didn't include transaction log backups. To make matters worse, a restore was impossible, because the tape drive had been out of order for a couple of weeks.
I said, 'Yes - I'll see what I can do', and dashed off to my keyboard to search the net. Eventually, I found some possible solutions. All relied on being able to modify the system tables, as some have mentioned above. I didn't want to try the procedure without checking with Microsoft, so for a few houndred dollars they provided me with a procedure practically similar to the ones on the net:
Microsofts disclaimer on DBCC REBUILD_LOG:
The Command is an undocumented and unsupported command that can be used in emergency situations where the database is offline because of some catastrophic problem with the transaction log. It is designed to destroy the entire contents of the current transaction log, rebuild a new one, and allow the user to bring the database back online. However, this may result in more data lost than if the last known, good backup was restored. As such, if you choose to use the Command with or without the assistance of Microsoft PSS, Microsoft strongly recommends you backup all of the database and transaction log files associated with the database for the Command as a method to recover from problems where the Command is being considered.
If this Command is successful and the database is brought back online, the database may be in a physically and logically inconsistent state. The ACID properties of Atomicity and Consistency are no longer guaranteed. The DBCC CHECKDB command can be used to determine what physical problems may exist. However, there is no method to determine what logical inconsistencies exist within the database aside from the user’s own manual inspection of the database tables. This means that there is no method to determine if the database pages that exist within the database reflect the correct committed state of the database when it was last taken offline.
Take a backup first!
1. Create a new DB with the same name as the original DB.
2. Stop SQL Server.
3. Replace the old MDF atop of the newly created one.
4. Start SQL Server, DB should be marked as suspect. Note that the status under master..sysdatabases of the DB is 1073741840.
5.Change the database context to Master and allow updates to system tables:
sp_configure 'allow updates', 1
reconfigure with override
-- Putting" a database in emergency mode
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
-- If you run DBCC REBUILD_LOG without setting the database in Emergency mode, the command does not work.
-- You do not receive an error, but the log is not rebuilt either.
-- The syntax for DBCC REBUILD_LOG is as follows:
-- Where <db_name> is the name of the database and <log_filename> is the physical path of the
-- new log file, not a logical file name. If you do not specify the full path, the new log is created in
-- the same directory as the primary database file (.mdf). Therefore, if the log file already exists in that
-- path, use a different name for the new log. You cannot move the log or delete the log because SQL
-- Server is using the log).
update sysdatabases set status = 0 where name = '<db_name>'
-- verify one row is updated before committing
RESTORE DATABASE <dbname> WITH RECOVERY
This procedure actually worked, the sysadmin didn't loose his job, and the company is thriving.