Attaching a database in Microsoft SQL Server 2008

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles