#0360 – SQL Server – sp_attach_db v/s CREATE DATABASE FOR ATTACH – Number of files limitation; Msg. 8144 (Procedure has too many arguments specified)

This post has been a long time coming. I am an advocate of using newer T-SQL and administrative features if situation and compatibility related business requirements allow. In implementation of this ideology the question always comes up from most of my team members as to why a change is required when the old syntax/methods work just as effectively. I often demonstrate the example provided below to them.

In development environments, we generally move databases across various instances via a simple detach-append process. The traditional approach is to use the system stored procedure sp_attach_db. When CREATE DATABASE was enhanced to include support of attaching a database, one of the obvious differences that came up was the number of database files that it supported for the ATTACH operation.

The sp_attach_db system stored procedure can only attach a maximum of 16 database files.

In this scenario, the newer option of CREATE DATABASE is a blessing for very large databases where we have multiple files spread across multiple file-groups and multiple drives.

Allow me to demonstrate this with an example.

USE master ;
GO
--Safety Check
IF DB_ID('AttachDBTest') IS NOT NULL 
    DROP DATABASE AttachDBTest ;
GO

--Create the test database that has more than 16 files
CREATE DATABASE AttachDBTest ON PRIMARY  
    (NAME = Attach1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach2,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach3,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach4,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach5,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach6,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach7,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach8,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach9,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach10,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    (NAME = Attach11,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat11.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach12,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach13,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach14,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach15,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach16,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach17,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach18,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach19,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach20,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) 
LOG ON 
   (NAME = Attachlog1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
--FOR ATTACH
;
GO

--Create Test Table
USE AttachDBTest;
GO
IF OBJECT_ID('dbo.TestTable','U') IS NULL
BEGIN
    CREATE TABLE dbo.TestTable (RecId INT NOT NULL IDENTITY(1,1),
                                RecValue NVARCHAR(20) DEFAULT 'AttachTest'
                               );

END
GO

--Insert Test Data
USE AttachDBTest;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
BEGIN
    EXEC sp_executesql N'INSERT INTO dbo.TestTable (RecValue) DEFAULT VALUES;';
END
GO 10

--Validate Test Data
USE AttachDBTest;
GO
SELECT * FROM dbo.TestTable;
GO

As seen from the screenshots below, the database creation was successful, and so was the insertion of test data.

Next, let us detach the database using the system stored procedure sp_detach_db.

--Detach the database
USE master;
GO
EXEC sp_detach_db @dbname = 'AttachDBTest', @skipchecks = 'true' ;
GO

--Confirm that the database has been detached
USE master;
GO
SELECT DB_ID('AttachDBTest') AS AttachDBTestDBID;
GO

Now, let us attach the database again with the sp_attach_db system stored procedure.

--Attach the database
USE master;
GO
EXEC sp_attach_db 
    @dbname = 'AttachDBTest', 
    @filename1 = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    @filename2 = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    @filename3 = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    @filename4 = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    @filename5 = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    @filename6 = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    @filename7 = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    @filename8 = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    @filename9 = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    @filename10 = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    @filename11 = 'C:SQLDatabasesDBFileAttachTestAttachdat11.ndf',
    @filename12 = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    @filename13 = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    @filename14 = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    @filename15 = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    @filename16 = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    @filename17 = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    @filename18 = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    @filename19 = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    @filename20 = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    @filename21 = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf';
GO

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

Now, let us try to attach the database via the CREATE DATABASE statement with the FOR ATTACH option.

--Attach the database using the CREATE DATABASE statement with the FOR ATTACH clause
USE master;
GO
CREATE DATABASE AttachDBTest ON PRIMARY  
    (NAME = Attach1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach2,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach3,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach4,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach5,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach6,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach7,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach8,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach9,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach10,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    (NAME = Attach11,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat11.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach12,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach13,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach14,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach15,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach16,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach17,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach18,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach19,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach20,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) 
LOG ON 
   (NAME = Attachlog1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
FOR ATTACH
;
GO

--Confirm that the database has been detached
USE master;
GO
SELECT DB_ID('AttachDBTest') AS AttachDBTestDBID;
GO

--Validate Test Data
USE AttachDBTest;
GO
SELECT * FROM dbo.TestTable;
GO

This example just demonstrates that when Microsoft recommends a switch from one syntax to another, there has to be some reason behind it and that newer options should be evaluated for replacing older ones.