How to attach .MDF file without .LDF file into Database SQL Server 2005

When your .LDF file growth and taking much of your hard disk space, deleting .LDF file is one of the solution. But how to attach .MDF file without .LDF file? In my experience I use to methods:

Method 1:

Attach the .MDF file like you usually attach the database file but you have to remove the .LDF file (do not specify a path to the non existant .LDF file).

Your database will get attached and a NEW log file (.ldf) will be created.

Method 2:
Use the following command and run it:
Sp_Attach_Db: Sp_Attach_Single_File_Db [ @Dbname = ] 'Dbname' , [ @Physname = ] 'Physical_Name'

Example:
EXEC sp_attach_single_file_db @dbname = 'Test',
@physname = 'D:\Microsoft SQL Server\MSSQL\Data\Test.mdf'

If Both Method doesn’t work you can try the steps below:

Problem when attaching .MDF file without LDF File
Attach database may not work in below situations:

1. If the database is not detached using sp_detach_db or using enterprise manager.

2. Sp_attach_single_file_db will fail incase if you have multiple LDF files associated.

How to Fix The Problem:

1. Create a new database with same MDF and LDF name

2. Stop SQL Server and delete the new MDF file and copy the old MDF file

3. Start SQL Server and see the status. If the database is suspect do the below steps:

- Setting the database status to emergency mode tells SQL Server to skip

- Automatic recovery and lets you access the data. To get your data, use this script:

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

- Create a new empty database and you might be able to use bulk copy program(BCP), simple SELECT commands, or use DTS to extract your data /objects from problematic database to new database.

Related Topics:
How to connect Delphi to database SQL Server
Select Distinct, Not In and Right Outer Join in SQL Command
How To Import Data Excel To SQL Server 2005
Function Date and Time in Foxpro
How to attach .MDF file without .LDF file into Database SQL Server 2005

How To Import Data Excel To SQL Server 2005

Microsoft SQL Server 2005

If you want to input Excel data in SQL Server database, the important point that you have to pay attention is column table and type data in SQL Server. The column in Excel must be the same as table in database SQL Server.

Ex: If you have File Excel such as below:


Then you should have database table in SQL Server such as below:


If you already have the same File Excel and SQL Server table then follow the step below for how to Import Data Excel To SQL Server

1. Right Click to the database where you want to import the database


2. Choose a Microsoft Excel as data source then browse the file, then click next
Note: in my experience, SQL server 2005 only can read Microsoft Excel data with MS Office 2003 and the old version (xls). When I try to browse the file which is using Excel 2007 (xlsx), it doesn’t work.



3. Choose SQL server Native Client as destination, then click next


4. Choose copy data from one or more tables or views, then click next


5. In Source, tick mark the sheet where you put excel data and in destination choose the table in SQL server, click next and finish


6. You can check the status whether the process Import data success or fail.


Related Topics:
How to connect Delphi to database SQL Server
Select Distinct, Not In and Right Outer Join in SQL Command
How To Import Data Excel To SQL Server 2005
Function Date and Time in Foxpro