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.

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.

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.

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.