Restore MS SQL Database without Transaction log file

Microsoft SQL server database is used by the variety of organization because of its advanced features and facilities. It stores all the data in the primary file which is also known as master database file and the file extension for the master database file is .mdf. SQL server also uses secondary database file (next database file) if the size of master database file is very large and the file extension for this is .ndf. All the modification related information is stored in the transaction log file. The transaction log file stores the data related to the modification of the database like insert, delete, update and many more. It is very helpful for the roll back of database if any corruption occurs in the database. Sometimes the transaction log file may be deleted by mistake or intentionally at this situation your database goes in halt situation and does not allow you to do any operation on the database.

you have a problem with your system and you take the backup of mdf file not the log file, and reinstall the SQL server database and try to restore or reattach the database and got an error message

“Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally”.

Solution: some of the possible solution is given below

1. In the above problem looks like just copy the mdf file and try to restore which would no be right, you need to try ATTACH to restore your database.
2. Run sp_attach_single_file_db command in the query analyzer to attach the mdf. It will automatically create the transaction log file in the same location.
3. DBCC command for rebuilding transaction load file.

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')


/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE MyDatabase SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO

Note: Always keep the copy of master database file in the safe location to avoid the database loss.

After performing the all above solutions and you are still not able to attach the database file that means there are some problem with your master database file, mdf file may be corrupt. At this situation restore your corrupt database from the updated backup. If there is no any updated backup then run DBCC CHECKDB command with proper repair clause if you understand the effect of DBCC CHECKDB. At last but not least you can try any third party SQL server recovery software to repair and restore your corrupt database.

Note: Always try demo version of any third party software first before downing the full version.