Attaching .MDF files and .LDF files of a database with FileGroups

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 illustrated how to handle multiple .MDF files and .LDF files when attaching it. Part 3 illustrated how to rebuild the log when attaching multiple .MDF files. This installment illustrates how to attach .MDF files and .LDF files of a database with FileGroups.

Let's assume that we have the database, MyDB2, with one .MDF file on the primary file group and two .NDF files on the secondary file group and one .LDF file. Execute the following transact SQL statement to create the database MyDB2.

USE [master]
GO

/****** Object:  Database [MyDB2]    
	Script Date: 08/11/2009 12:07:59 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB2')
DROP DATABASE [MyDB2]
GO


CREATE DATABASE MyDB2
ON PRIMARY
  ( NAME='MyDB2_Primary',
    FILENAME=
       'c:\data\MyDB2_Prm.mdf',
    SIZE=2MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP MyDB2_FG1
  ( NAME = 'MyDB2_FG1_Dat1',
    FILENAME =
       'c:\data\MyDB2_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
  ( NAME = 'MyDB2_FG1_Dat2',
    FILENAME =
       'c:\data\MyDB2_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='MyDB2_log',
    FILENAME =
       'c:\data\MyDB2.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO

Now let's detach the MyDB2 database using the sp_deatch_db system stored procedure and attach it using the sp_attach_db system stored procedure as shown below. Execute the following Transact SQL statement.

use master
go
sp_detach_db [MyDB2]
go
sp_attach_db 'MyDB2','c:\data\MyDB2_Prm.mdf',
'c:\data\MyDB2_FG1_1.ndf', 
'c:\data\MyDB2_FG1_2.ndf', 
'c:\data\MyDB2.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 [MyDB2]
go

CREATE DATABASE MyDB2
ON PRIMARY
(FILENAME =       'c:\data\MyDB2_Prm.mdf'),
(FILENAME =       'c:\data\MyDB2_FG1_1.ndf'),
(FILENAME =       'c:\data\MyDB2_FG1_2.ndf'),
(FILENAME =       'c:\data\MyDB2.ldf')
for attach
go

In Figure 1.0, you see that the file group, FILEGROUP MyDB2_FG1, is still intact after attaching it using the sp_attach_db system stored procedure and using the "Create Database" with "for attach" clause.

Now let us detach the "MyDB2" database using the sp_detach_db system stored procedure, delete the .LDF file and try to rebuild the log using sp_attach_db and using "Create Database" with the "for ATTACH_REBUILD_LOG " clause.

use master
go
sp_detach_db 'MyDB2'
go
exec master..xp_cmdshell 'del c:\data\MyDB2.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 the Windows Explorer "Del" command at the MS-DOS command prompt.

Now let's attach the .MDF file and .NDF files using the sp_attach_db system stored procedure. Execute the following command.

sp_attach_db 'MyDB2',
'c:\data\MyDB2_Prm.mdf','c:\data\MyDB2_FG1_1.ndf'
,'c:\data\MyDB2_FG1_2.ndf'

Result:

File activation failure. The physical file name "c:\data\MyDB2.ldf" may be incorrect.
New log file 'c:\data\MyDB2_log.LDF' was created.

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

You can attach the same database .MDF file only using "Create database" command with

use master
go
sp_detach_db 'MyDB2'
go
exec master..xp_cmdshell 'del c:\data\MyDB2_log.LDF'
go


CREATE DATABASE MyDB2
ON PRIMARY
(FILENAME =       'c:\data\MyDB2_Prm.mdf'),
(FILENAME =       'c:\data\MyDB2_FG1_1.ndf'),
(FILENAME =       'c:\data\MyDB2_FG1_2.ndf')
for ATTACH_REBUILD_LOG
go

Result:

File activation failure. The physical file name "c:\data\MyDB2_log.LDF" may be incorrect.
New log file 'c:\data\MyDB2_log.LDF' was created.

Keep in mind that when attaching .MDF files and .LDF files from previous versions such as SQL Server 2000 and SQL Server 2005, SQL server automatically upgrades the database to SQL Server 2008.

However, you cannot attach SQL Server 2008's .MDF file and .LDF files to SQL Server 2005 and SQL Server 2000 servers.

Warning: Please try all of the above-illustrated commands from all installments of this series on your test or QA servers first. Do not try to execute system stored procedures like sp_detach_db and xp_cmdshell on a production machine without understanding the outcome.

Conclusion

Part 1 of this article 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 illustrated how to handle multiple .MDF files and .LDF files when attaching it. Part 3 illustrated how to rebuild the log when attaching multiple .MDF files. This installment illustrated how to attach .MDF files and .LDF files of a database with FileGroups.

» See All Articles by Columnist MAK