Method 1: Using SQL Server Management Studio – Attach a SQL Server Database without a Transactional Log File
1. Connect to SQL Server 2008 Instance using SQL Server Management Studio.
2. In Object Explorer, right click Databases node and select “Attach…” option from the drop down list as shown in the snippet below.
3. This will open up an Attach Databases window as shown in the below snippet.
4. In the Attach Databases window click on “Add…” button as highlighted in the above snippet to open the Locate Databases Files window as shown below.
5. In Locate Databases Files window you need to browse and locate the MDF file which is relevant to the database you want to attach and then click OK. In our example I am using the “ProductsDB.mdf” file.
6. In the Attach Databases window; you will see that SQL Server informs us that the log file is not found, in our case the missing file is “ProductsDB_Log.LDF”. In order to attach the database without the ProductsDB_Log.LDF transaction log file you need to select the file as highlighted in the below snippet and click the “Remove” button and finally click OK to attach ProductsDB database without the transaction log file.
7. That’s it you have successfully attached a database without using the transaction log file. SQL Server will create a new transaction log file for you and will place the file in the same folder as the data file.
Method 2: Using T-SQL – Attach a SQL Server Database without a Transaction Log File
We can also attach a SQL Server database without a transaction log file using the below T-SQL code.
In this first script we are using the CREATE DATABASE with ATTACH option and specifying the name and location of the mdf file that we have for this database.
CREATE DATABASE [ProductsDB] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf’ )
Once the above T-SQL code has executed successfully you will get the below message which informs you that SQL Server has created a new transaction log file for the database.
File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf” may be incorrect.
New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.LDF’ was created.
We can check the logical and physical integrity of all the objects within the database by executing a DBCC CHECKDB. In our case we are using the “ProductsDB” database.
DBCC CHECKDB (‘ProductsDB’)
You should know about this:
- The only time that you can do this is when the DB was shut down cleanly before the log file was lost. It’s still not a good idea. You need the transaction log file as well as the data files because SQL Server references the log file when attaching the DB, it’s the crash recovery process. While attaching a data file without the log file may be possible in some circumstances, it is not a recommended approach and is only for cases when the log file has been damaged or lost due to hardware problems and there are no backups available. Of course, you cannot have a database without a log file, so SQL Server just recreates a log file when you attach the database.
- Attaching a data file without the log file breaks the log chain and may render the database transactionally or structurally inconsistent depending on the state of the database before the log file was lost. It may also result in the database failing to attach at all, no matter what steps are taken.
- You can detach the data and log files for a database but you need to ensure that these log files either have been detached from a server, or copied after shutting down the server. This ensures that all transactions for that database are complete. Move them to another server and put them on that server in a process that is much faster than a restore, although not necessarily as safe, since you have to copy or move your files. Note again that you can only move upwards, not downwards in SQL Server versions.