Rebuilding the log when attaching multiple .MDF files

September 2, 2009

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 the database. Part 3 of the series illustrates how to rebuild the log when attaching multiple .MDF files.

Now let's assume that we have the database, MyDB1, with eighteen .MDF files and seventeen .LDF files. Execute the following command.

USE [master]
GO

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


CREATE DATABASE MyDB1
ON 
( NAME = MyDB1_dat1,FILENAME = 'C:\Data\MyDB1.mdf'),
( NAME = MyDB1_dat2,FILENAME = 'C:\Data\MyDB2.mdf'),
( NAME = MyDB1_dat3,FILENAME = 'C:\Data\MyDB3.mdf'),
( NAME = MyDB1_dat4,FILENAME = 'C:\Data\MyDB4.mdf'),
( NAME = MyDB1_dat5,FILENAME = 'C:\Data\MyDB5.mdf'),
( NAME = MyDB1_dat6,FILENAME = 'C:\Data\MyDB6.mdf'),
( NAME = MyDB1_dat7,FILENAME = 'C:\Data\MyDB7.mdf'),
( NAME = MyDB1_dat8,FILENAME = 'C:\Data\MyDB8.mdf'),
( NAME = MyDB1_dat9,FILENAME = 'C:\Data\MyDB9.mdf'),
( NAME = MyDB1_dat10,FILENAME = 'C:\Data\MyDB10.mdf'),
( NAME = MyDB1_dat11,FILENAME = 'C:\Data\MyDB11.mdf'),
( NAME = MyDB1_dat12,FILENAME = 'C:\Data\MyDB12.mdf'),
( NAME = MyDB1_dat13,FILENAME = 'C:\Data\MyDB13.mdf'),
( NAME = MyDB1_dat14,FILENAME = 'C:\Data\MyDB14.mdf'),
( NAME = MyDB1_dat15,FILENAME = 'C:\Data\MyDB15.mdf'),
( NAME = MyDB1_dat16,FILENAME = 'C:\Data\MyDB16.mdf'),
( NAME = MyDB1_dat17,FILENAME = 'C:\Data\MyDB17.mdf'),
( NAME = MyDB1_dat18,FILENAME = 'C:\Data\MyDB18.mdf')
LOG ON
( NAME = MyDB1_log1,FILENAME = 'C:\Data\MyDB1.ldf'),
( NAME = MyDB1_log2,FILENAME = 'C:\Data\MyDB2.ldf'),
( NAME = MyDB1_log3,FILENAME = 'C:\Data\MyDB3.ldf'),
( NAME = MyDB1_log4,FILENAME = 'C:\Data\MyDB4.ldf'),
( NAME = MyDB1_log5,FILENAME = 'C:\Data\MyDB5.ldf'),
( NAME = MyDB1_log6,FILENAME = 'C:\Data\MyDB6.ldf'),
( NAME = MyDB1_log7,FILENAME = 'C:\Data\MyDB7.ldf'),
( NAME = MyDB1_log8,FILENAME = 'C:\Data\MyDB8.ldf'),
( NAME = MyDB1_log9,FILENAME = 'C:\Data\MyDB9.ldf'),
( NAME = MyDB1_log10,FILENAME = 'C:\Data\MyDB10.ldf'),
( NAME = MyDB1_log11,FILENAME = 'C:\Data\MyDB11.ldf'),
( NAME = MyDB1_log12,FILENAME = 'C:\Data\MyDB12.ldf'),
( NAME = MyDB1_log13,FILENAME = 'C:\Data\MyDB13.ldf'),
( NAME = MyDB1_log14,FILENAME = 'C:\Data\MyDB14.ldf'),
( NAME = MyDB1_log15,FILENAME = 'C:\Data\MyDB15.ldf'),
( NAME = MyDB1_log16,FILENAME = 'C:\Data\MyDB16.ldf'),
( NAME = MyDB1_log17,FILENAME = 'C:\Data\MyDB17.ldf')
GO

Let's detach the database and delete all of the .MDF files.

use master
go
sp_detach_db MyDB1
go
exec master..xp_cmdshell 'Del c:\data\mydb1.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb2.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb3.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb4.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb5.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb6.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb7.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb8.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb9.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb10.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb11.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb12.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb13.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb14.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb15.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb16.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb17.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 try to attach only .MDF files using sp_attach_db. Execute the following command.

sp_attach_db 'MyDB1','C:\Data\MyDB1.mdf',
'C:\Data\MyDB2.mdf','C:\Data\MyDB3.mdf',
'C:\Data\MyDB4.mdf','C:\Data\MyDB5.mdf',
'C:\Data\MyDB6.mdf','C:\Data\MyDB7.mdf',
'C:\Data\MyDB8.mdf','C:\Data\MyDB9.mdf',
'C:\Data\MyDB10.mdf','C:\Data\MyDB11.mdf',
'C:\Data\MyDB12.mdf','C:\Data\MyDB13.mdf',
'C:\Data\MyDB14.mdf','C:\Data\MyDB15.mdf',
'C:\Data\MyDB16.mdf','C:\Data\MyDB17.mdf',
'C:\Data\MyDB18.mdf'

Result:

Msg 8144, Level 16, State 2, Procedure sp_attach_db, Line 0
Procedure or function sp_attach_db has too many arguments specified.

Since we are attaching more files, we could not use the sp_attach_single_file_db system stored procedure. So let's try to attach them using the "Create database" statement with "for ATTACH_REBUILD_LOG". Execute the following transact SQL statement.

CREATE DATABASE MyDB1
ON 
(FILENAME = 'C:\Data\MyDB1.mdf'),
(FILENAME = 'C:\Data\MyDB2.mdf'),
(FILENAME = 'C:\Data\MyDB3.mdf'),
(FILENAME = 'C:\Data\MyDB4.mdf'),
(FILENAME = 'C:\Data\MyDB5.mdf'),
(FILENAME = 'C:\Data\MyDB6.mdf'),
(FILENAME = 'C:\Data\MyDB7.mdf'),
(FILENAME = 'C:\Data\MyDB8.mdf'),
(FILENAME = 'C:\Data\MyDB9.mdf'),
(FILENAME = 'C:\Data\MyDB10.mdf'),
(FILENAME = 'C:\Data\MyDB11.mdf'),
(FILENAME = 'C:\Data\MyDB12.mdf'),
(FILENAME = 'C:\Data\MyDB13.mdf'),
(FILENAME = 'C:\Data\MyDB14.mdf'),
(FILENAME = 'C:\Data\MyDB15.mdf'),
(FILENAME = 'C:\Data\MyDB16.mdf'),
(FILENAME = 'C:\Data\MyDB17.mdf'),
(FILENAME = 'C:\Data\MyDB18.mdf')
for ATTACH_REBUILD_LOG
GO

Result:

File activation failure. The physical file name "C:\Data\MyDB1.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB2.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB3.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB4.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB5.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB6.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB7.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB8.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB9.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB10.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB11.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB12.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB13.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB14.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB15.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB16.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB17.ldf" may be incorrect.
New log file 'C:\Data\MyDB1_log.LDF' was created.

Conclusion

This article has illustrated how to rebuild the log when attaching multiple .MDF files. The next part of the series is going to illustrate attaching multiple .MDF and .LDF files of the database with file groups.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers