How to Attach Database Without a Transaction Log File in SQL Server
Aug 07, 2012
This article demonstrates how to attach a database in SQL Server when transaction log file is missing. The steps mentioned in this article are applicable on SQL Server 2005 and higher versions. To know more about how to Attach Database in SQL Server with transaction log file refer the following article “Attach Database in SQL Server”.
Different ways to Attach Database without a transaction log file in SQL Server
- Attach Database without a transaction log file in SQL Server Using SQL Server Management Studio (SSMS)
- Attach Database without a transaction log file in SQL Server Using TSQL Script
Permissions to Attach Database in SQL Server
You need membership in the db_owner fixed database role to Attach Database in SQL Server.
You will not be able to detach and attach database from a higher version of SQL Server to a lower version of SQL Server this is by design from Microsoft. To know more read the following article “Why an SQL Server Database from a higher version cannot be attached onto a lower version of SQL Server?”
Downgrade of a database from higher version to lower version cannot be achieved using Database Backup and Restore Method and also by using Database Detach and Attach Method. However, you can downgrade a database to a lower version using SQL Server Integration Services. To know more read "How to Downgrade SQL Server Database Using SQL Server Integration Services".
Let us go through each of the above mentioned options in detail.
Attach Database without a transaction log file in SQL Server Using SQL Server Management Studio (SSMS)
1. In Object Explorer, right click the Databases node and then click Attach… from the drop down menu as shown in the clip below.
2. In Attach Databases dialog box, click Add… button.
3. In Locate Database Files dialog box, browse to the location where you database MDF file is available and then choose the file and click OK and to return to parent Attach Databases dialog box.
4. In Attach Database dialog box; you will see under database details as highlighted that SQL Server is unable to locate the LDF file. To attach the database without the LDF file select the LDF file and then click remove button. Finally, click OK to successfully attach the database. In this case SQL Server will create a new log file while attaching the database.
Important Note: -The above mentioned steps will work only when your database which was cleanly shutdown and you have the .MDF file of the database.
Attach Database without a transaction log file in SQL Server Using TSQL Script
Using the below script you can attach database in SQL Server which has a missing transaction log file.
USE [master] GO CREATE DATABASE [AdventureWorksDW2008R2] ON ( FILENAME = N'D:\Downloads\SQL2008R2SampleDBs\AdventureWorksDW2008R2_Data.mdf' ) FOR ATTACH GO
Execute DBCC CHECKDB
Once you have successfully attached a MDF file without a transactional log (LDF) file go ahead and execute DBCC CHECKDB to check the logical and physical integerity of all the objects in the specified database.
Use MyTechMantra GO DBCC CHECKDB GO
Reference:- Database Detach and Attach (SQL Server)
Continue Free Learning...
Geeks who read this article also read…
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…