Sometimes, the transaction log of your SQL Server database gets deleted or you lose the drive that contains the log file. In either of the cases, your SQL database goes into the ‘suspect’ mode and starts behaving abnormally. At this stage, you should not choose to restore database from a backup. This will make you lose all changes made to the database since the last backup.
An ideal workaround for this is to detach the database and then reattach the database to the server without the log. You can do so by using SQL Server Management Studio or via T-SQL. Let us quickly summarize the series of steps to be performed for restoring SQL database (‘.MDF’ file) to the server using both these methods.
Using SQL Server Management Studio
- Note that the procedure applies both for SQL Server 2005 and SQL Server 2008.Connect your database to SQL Server 2008 Instance through SQL Server Management Studio.
- Open ‘Object Explorer’, right-click databases, and then choose ‘Attach’ in the dropdown list. You will see an ‘Attach Databases’ window on your screen.
- Click ‘Add..’ button in the ‘Attach Databases’ window. This will open the ‘Locate Databases Files’ window.
- In the ‘Locate Databases Files’ window, find and locate the MDF file corresponding to your database that you need to attach and click ‘OK’.
- In the ‘Attach Databases’ window, you will see that the SQL Server displays the message ‘Not Found’ against the LDF file corresponding to your database.
- If you need to attach the database without the LDF file, then you should select this file in the database details section and click the ‘Remove’ button. Next, click ‘OK’ to attach the database without transaction log.
- After completion of the process, SQL Server creates a new transaction log and puts this file in the same folder where your database is stored.
You can use CREATE DATABASE with ATTACH option to attach an SQL database without a transactional log file. To do this you need to provide the name and location of the MDF file corresponding to your database. Run the folowing script:
CREATE DATABASE [Databasename] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Databasename.mdf’ )
GO ‘Databasename’ is the name of the database that you need to attach. If the above script ran successfully, you will receive the following message: File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Databasename_log.ldf” may be incorrect. New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Databasename_log.LDF’ was created. You can run DBCC CHECKDB against the database in order to verify its logical and physical integrity as follows: ‘DBCC CHECKDB (‘Databasename’) GO’
- If the SQL database you need to attach has multiple transaction log files, you should use the ATTACH_REBUILD_LOG option with the CREATE DATABASE command.
- If one of the data files for your database is missing, you cannot use this method. The method can only rebuild transaction log files, it cannot recreate your data files.
This way you can ideally restore an SQL database without transaction log to the server again.
Jyoti Prakash is Sr. Technical writer who has written several article on SQL server database recovery scenarios.