Q
Problem solve Get help with specific problems with your technologies, process and projects.

Attaching a database that has missing log file

In SQL Server 2000, I have copied the data file (*.mdf) but was not able to copy the log file (*ldf). I am trying now to attach the database to another server, but it is returning an error message that the log file is different. How can I attach a database that has a missing or invalid log file?

You are in luck as long as the sp_detach_db was executed properly. If so, use the sp_attach_single_file_db command to attach a database with a single data file (*.mdf). When you use this command, SQL Server will create a log file and attempt to resolve any data related discrepancies. The syntax for this command would be:

 USE Tempdb
GO
EXEC master.dbo.sp_attach_single_file_db @dbname = 'DatabaseName', 
@physname = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\DatabaseName.mdf'
 GO
Based on the circumstances, it may be beneficial to ensure that the database does not have any consistency or allocation issues by executing the following commands:

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close