SQL Server: How to attach database without Log File

Sometimes,  you may encounter a problem with attaching database to your SQL Server without Log File.
Especially when you attach the database with recovery model Bulk-Logged or Full.
Make sure you change the database recovery model to simple first before you detach it. So you will not encounter error when attach back without log file.

 

Step by Step to recover the database without log file
1. Rename the existing database data file ([DBNAME].MDF) file to [DBNAME].MDF_OLD
2. Create a new database with the same name [DBNAME]
3. Stop SQL Server
4. Rename new created database data file [DBNAME].MDF to [DBNAME].MDF!
5. Rename new created database log file [DBNAME].LDF to [DBNAME].LDF!
6. Rename back the original database data file [DBNAME].MDF_OLD to [DBNAME].MDF
7. Start SQL Server and  you should see the db in suspect mode
8. Change Database Context to Master and allow update system table

Use Master
Go
sp_configure ‘allow updates’, 1
reconfigure with override
Go

9. Set the database in emergency mode (bypass recovery) mode

          select * from sysdatabases where name = ‘[DBNAME]’
          begin tran
          update sysdatabases set status = 32768 where name ='[DBNAME]’
          — Make sure only 1 row is affected
          commit tran

10. Restart SQL Server
11. Rebuilt the log file:

DBCC TRACEON (3604)
DBCC REBUILD_LOG(‘[DBNAME]’,'<log_filename_physical_path>’)
Go

If the command is successful, the following message appears:
Warning: The log for database ‘<db_name>’ has been rebuilt.
Transactional consistency has been lost. DBCC CHECKDB should be run to
validate physical consistency. Database options will have to be reset,
and extra log files may need to be deleted.

11. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency

sp_dboption ‘[DBNAME]’, ‘single user’, ‘true’
DBCC checkdb(‘[DBNAME]’)
Go

begin tran
update sysdatabases set status = 1073741840 where name ='[db_name]’
— verify one row is updated before committing
commit tran
Go

12. Turn off the updates to system tables

sp_configure ‘allow updates’, 0
reconfigure with override
Go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s