Attach database in Microsoft SQL Server 2008

This article will illustrate the different usage methods of the 'For Attach' clause to overcome the limitations encountered when using sp_attach_db and sp_attach_single_file_db.

In SQL Server 7.0, Microsoft introduced two system storage procedures, sp_attach_db and sp_attach_single_file_db, making it easier for database administrators to perform the following tasks:

1. Attach the .MDF and .LDF files directly to the server using the sp_attach_db system stored procedure.
2. Only Attach .MDF files using system storage procedure sp_attach_single_file_db.
3. Detach the database from the server using sp_detach_db. Copy the .MDF files to another server and then attach them back to both servers using the sp_attach_db system stored procedure.

Although both of these procedures are useful for database administrators, there are certain limitations, which are:

1. You cannot attach multiple log files
2. You cannot attach more than 16 files

In SQL Server 2008, Microsoft announced that the above system storage procedures would be 'dropped'. Instead, Microsoft added the ' For Attach ' clause in SQL's 'Create Database' statement.

This article will illustrate the different usage methods of the 'For Attach' clause to overcome the limitations encountered when using sp_attach_db and sp_attach_single_file_db.

Suppose we have the database 'MyDB1' with a .MDF file and a .LDF file at location 'C: Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database DataData File for Monthly ArchiveMicrosoft SQL Server 2008 Data File'. Initialize the database using the following Transact SQL code.

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 try detach this database and attach it again using sp_detach_db and sp_attach_db. Run the following T-SQL code:

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 can attach the database above by using the 'Create database' command with the 'For Attach' clause as follows:

use master
go
sp_detach_db 'MyDB1'
go

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

go

Now detach the database MyDB1 then delete the .LDF file, then reattach it using the sp_attach_single_file_db system stored procedure by executing the following T-SQL code:

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

Note: In the above code, I use xp_cmdshell to delete the .LDF file. You will receive the following error message if xp_cmdshell is not enabled:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server bị thoát vào tiến trình 'sys.xp_cmdshell' component 'xp_cmdshell' because this component is turned off as part of the configuration security for this server.A system administrator không thể sử dụng sử dụng 'xp_cmdshell' by using sp_configure.For thêm thông tin về quyền hạn xp_cmdshell, see "Surface Area Configuration" in SQL Server Books Online.

You can enable xp_cmdshell by using the following T-SQL code:

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 can delete the .LDF file by using the 'Del' command from the MS-DOS command prompt.

Now attach the .MDF file separately using sp_attach_single_file_db. Run the following statement:

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. Tên tập tin hợp lệ
"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 Files Microsoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF 'was created.

You can attach the database's .MDF file by using the 'Create database' command with the 'For ATTACH_REBUILD_LOG' clause as follows:

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 is recreated, SQL Server automatically adds the '_log' suffix to the file name.

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

Result:

File activation failure. Tập tin cơ sở dữ liệu tên "C: Program Files Microsoft 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 Files Microsoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database
DataData File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF 'was created.

Conclude

The above article illustrated how to use the command 'Create Database' with the clause 'For Attach' and 'for ATTACH_REBUILD_LOG' for a single .MDF file and a single .LDF file. In the next article, you will be introduced to how to work with multiple .MDF and .LDF files.