Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 2, 2009

Rebuilding the log when attaching multiple .MDF files

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM