Rebuilding the log when attaching multiple .MDF files

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:DataMyDB1.mdf’),
( NAME = MyDB1_dat2,FILENAME = ‘C:DataMyDB2.mdf’),
( NAME = MyDB1_dat3,FILENAME = ‘C:DataMyDB3.mdf’),
( NAME = MyDB1_dat4,FILENAME = ‘C:DataMyDB4.mdf’),
( NAME = MyDB1_dat5,FILENAME = ‘C:DataMyDB5.mdf’),
( NAME = MyDB1_dat6,FILENAME = ‘C:DataMyDB6.mdf’),
( NAME = MyDB1_dat7,FILENAME = ‘C:DataMyDB7.mdf’),
( NAME = MyDB1_dat8,FILENAME = ‘C:DataMyDB8.mdf’),
( NAME = MyDB1_dat9,FILENAME = ‘C:DataMyDB9.mdf’),
( NAME = MyDB1_dat10,FILENAME = ‘C:DataMyDB10.mdf’),
( NAME = MyDB1_dat11,FILENAME = ‘C:DataMyDB11.mdf’),
( NAME = MyDB1_dat12,FILENAME = ‘C:DataMyDB12.mdf’),
( NAME = MyDB1_dat13,FILENAME = ‘C:DataMyDB13.mdf’),
( NAME = MyDB1_dat14,FILENAME = ‘C:DataMyDB14.mdf’),
( NAME = MyDB1_dat15,FILENAME = ‘C:DataMyDB15.mdf’),
( NAME = MyDB1_dat16,FILENAME = ‘C:DataMyDB16.mdf’),
( NAME = MyDB1_dat17,FILENAME = ‘C:DataMyDB17.mdf’),
( NAME = MyDB1_dat18,FILENAME = ‘C:DataMyDB18.mdf’)
LOG ON
( NAME = MyDB1_log1,FILENAME = ‘C:DataMyDB1.ldf’),
( NAME = MyDB1_log2,FILENAME = ‘C:DataMyDB2.ldf’),
( NAME = MyDB1_log3,FILENAME = ‘C:DataMyDB3.ldf’),
( NAME = MyDB1_log4,FILENAME = ‘C:DataMyDB4.ldf’),
( NAME = MyDB1_log5,FILENAME = ‘C:DataMyDB5.ldf’),
( NAME = MyDB1_log6,FILENAME = ‘C:DataMyDB6.ldf’),
( NAME = MyDB1_log7,FILENAME = ‘C:DataMyDB7.ldf’),
( NAME = MyDB1_log8,FILENAME = ‘C:DataMyDB8.ldf’),
( NAME = MyDB1_log9,FILENAME = ‘C:DataMyDB9.ldf’),
( NAME = MyDB1_log10,FILENAME = ‘C:DataMyDB10.ldf’),
( NAME = MyDB1_log11,FILENAME = ‘C:DataMyDB11.ldf’),
( NAME = MyDB1_log12,FILENAME = ‘C:DataMyDB12.ldf’),
( NAME = MyDB1_log13,FILENAME = ‘C:DataMyDB13.ldf’),
( NAME = MyDB1_log14,FILENAME = ‘C:DataMyDB14.ldf’),
( NAME = MyDB1_log15,FILENAME = ‘C:DataMyDB15.ldf’),
( NAME = MyDB1_log16,FILENAME = ‘C:DataMyDB16.ldf’),
( NAME = MyDB1_log17,FILENAME = ‘C:DataMyDB17.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:datamydb1.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb2.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb3.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb4.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb5.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb6.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb7.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb8.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb9.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb10.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb11.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb12.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb13.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb14.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb15.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb16.ldf’
go
exec master..xp_cmdshell ‘Del c:datamydb17.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:DataMyDB1.mdf’,
‘C:DataMyDB2.mdf’,’C:DataMyDB3.mdf’,
‘C:DataMyDB4.mdf’,’C:DataMyDB5.mdf’,
‘C:DataMyDB6.mdf’,’C:DataMyDB7.mdf’,
‘C:DataMyDB8.mdf’,’C:DataMyDB9.mdf’,
‘C:DataMyDB10.mdf’,’C:DataMyDB11.mdf’,
‘C:DataMyDB12.mdf’,’C:DataMyDB13.mdf’,
‘C:DataMyDB14.mdf’,’C:DataMyDB15.mdf’,
‘C:DataMyDB16.mdf’,’C:DataMyDB17.mdf’,
‘C:DataMyDB18.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:DataMyDB1.mdf’),
(FILENAME = ‘C:DataMyDB2.mdf’),
(FILENAME = ‘C:DataMyDB3.mdf’),
(FILENAME = ‘C:DataMyDB4.mdf’),
(FILENAME = ‘C:DataMyDB5.mdf’),
(FILENAME = ‘C:DataMyDB6.mdf’),
(FILENAME = ‘C:DataMyDB7.mdf’),
(FILENAME = ‘C:DataMyDB8.mdf’),
(FILENAME = ‘C:DataMyDB9.mdf’),
(FILENAME = ‘C:DataMyDB10.mdf’),
(FILENAME = ‘C:DataMyDB11.mdf’),
(FILENAME = ‘C:DataMyDB12.mdf’),
(FILENAME = ‘C:DataMyDB13.mdf’),
(FILENAME = ‘C:DataMyDB14.mdf’),
(FILENAME = ‘C:DataMyDB15.mdf’),
(FILENAME = ‘C:DataMyDB16.mdf’),
(FILENAME = ‘C:DataMyDB17.mdf’),
(FILENAME = ‘C:DataMyDB18.mdf’)
for ATTACH_REBUILD_LOG
GO

Result:


File activation failure. The physical file name “C:DataMyDB1.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB2.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB3.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB4.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB5.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB6.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB7.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB8.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB9.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB10.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB11.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB12.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB13.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB14.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB15.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB16.ldf” may be incorrect.
File activation failure. The physical file name “C:DataMyDB17.ldf” may be incorrect.
New log file ‘C:DataMyDB1_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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles