Attaching a database in Microsoft SQL Server 2008

August 12, 2009

In SQL Server 7.0, Microsoft introduced sp_attach_db and sp_attach_single_file_db system procedures. It was very convenient for SQL Server database administrators to perform the following tasks:

• Attach .MDF and .LDF files directly to the server using sp_attach_db system stored procedure.
• Attach only .MDF files using sp_attach_single_file_db system stored procedure.
• Detach the databases from one server using sp_detach_db, copy the .MDF files to another server and then reattach the files on both servers using sp_attach_db system stored procedure.

Though it was useful for SQL Server Database administrators, there are limitations when using both these procedures. The limitations are:

• You cannot attach multiple log files
• You cannot attach more than 16 files

In SQL Server 2008, Microsoft announced that the above system-stored procedures will be deprecated in future releases. Alternatively, they added a clause "For Attach" in the "Create Database" SQL Statement.

This series is going to illustrate the various methods of using the "For Attach" clause in order to overcome all of the limitations that face us when using sp_attach_db and sp_attach_single_file_db.

Let's assume that we have the database "MyDB1" with one .MDF file and one .LDF file. Create the database using the following Transact SQL statement. Let's also assume that the location of .MDF and .LDF files is 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database DataData File for Monthly ArchiveMicrosoft SQL Server 2008 Data File'.

Use Master
go
CREATE DATABASE MyDB1
ON 
( NAME = MyDB1_dat,
    FILENAME = 'C:Program Files
	Microsoft SQL ServerMSSQL10.SQL2008MSSQLDATA
	Summary Database DataData File for Monthly Archive
	Microsoft SQL Server 2008 Data FileMyDB1.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = MyDB1_log,
    FILENAME = 'C:Program Files
	Microsoft SQL ServerMSSQL10.SQL2008MSSQLDATA
	Summary Database DataData File for Monthly Archive	
	Microsoft SQL Server 2008 Data FileMyDB1.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )
GO

Now let's detach this database and try to reattach using sp_detach_db and sp_attach_db. Execute the following Transact SQL Statement.

use master
go
sp_detach_db 'MyDB1'
go

sp_attach_db 'MyDb1',
'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf',
'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.ldf'
GO

You could attach the same database files using the "Create database" command with the "For Attach" clause as shown below.

use master
go
sp_detach_db 'MyDB1'
go

CREATE DATABASE MyDB1
ON 
(
FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf'
), 
(
FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.ldf'
) for Attach

go

Now let's detach the database MyDB1 and then delete the .ldf file, then reattach it using the sp_attach_single_file_db system stored procedure by executing the following TSQL command.

use master
go
sp_detach_db 'MyDB1'
go
exec master..xp_cmdshell 'del "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.ldf"'
go

Note: I am using xp_cmdshell to delete the .ldf file. You will get the following error, if xp_cmdshell is not enabled.

Error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' 
	because this component is turned off as part of the security configuration for 
	this server. A system administrator can enable the use of 'xp_cmdshell' by using 
	sp_configure. For more information about enabling 'xp_cmdshell', see 
	"Surface Area Configuration" in SQL Server Books Online.

You can enable xp_cmdshell using the following Transact SQL Statement.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override
go

Alternatively, you could delete the .ldf file using Windows Explorer's "Del" command at the MS-DOS command prompt.

Now let's attach the .MDF file only using sp_attach_single_file_db. Execute the following command as shown below.

use master
go
sp_attach_single_file_db 'MyDB1',
'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf'
go

Result

File activation failure. The physical file name 
	"C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.ldf" may be incorrect.
New log file 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF' was created.

You could attach the same database .MDF file only by using the "Create database" command with the "For ATTACH_REBUILD_LOG" clause as shown below.

use master
go
sp_detach_db 'MyDB1'
go
exec master..xp_cmdshell 'del 
	"C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.ldf"'
go

Note: When the log file was recreated, SQL Server automatically suffixed "_log" to the log file name.

CREATE DATABASE MyDB1
ON 
(
FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
	Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf'
) for ATTACH_REBUILD_LOG

Result:

File activation failure. The physical file name "C:Program FilesMicrosoft SQL Server
	MSSQL10.SQL2008MSSQLDATASummary Database DataData File for 
	Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF" may be incorrect.
New log file 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database 
	DataData File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF' was created.

Conclusion

Part 1 of this series illustrated the use of the "Create Database" statement "For Attach" and "for ATTACH_REBUILD_LOG" for a single .MDF file and single .LDF file. Part 2 of the series is going to illustrate how to handle multiple .MDF files and .LDF files.

» See All Articles by Columnist MAK