Create Database ... For Attach

Has anyone ever used the CREATE DATABASE statement with the FOR ATTACH clause?

I have a database with something like 128 physical data files which I have to move (detach, move the physical files and then re-attach). Since the limit for sp_attach_db is 16 files, I am forced to use the FOR ATTACH option. I was wondering if anyone had ever done this and if there were any hints and/or recommendations you might be willing to share...

Regards,

hmscott128 files?

Dude...how big is this thing?

what's the hardware config?

No I haven't done this...|||Sigh, it's another of my "inherited" databases. Worse, it's supposedly a "vendor supported" solution. Let's just not go there.

There are actually two databases on this one server (one has a mere 75 files, the other has 128). The bigger of the two is used to store images of...um...people.

The bigger database is ~240 GB. According to the vendor it will grow by approximately 50 - 80 GB per year. We are in the process of moving it from an "all on board" disk solution to a CX 500 SAN.

Oh, well, I am going to create a sandbox version of the db with ~32 files and play around with detaching an re-attaching it. As always thanks for your response.

Regards,

hmscott

128 files?

Dude...how big is this thing?

what's the hardware config?

No I haven't done this...|||um...people...just images...no mpgs?|||The images are of people you would probably rather not routinely associate with.

hmscott

PS. I just test the process on a bunch of empty files; it's a cinch. Here's the SQL..

Create the initial database on 32 data files and two log files:

-- =============================================
-- Create database on multiple data and transaction log files
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO

CREATE DATABASE MultiFile
ON PRIMARY
( NAME = FileName1,
FILENAME = N'e:\MSSQL\Data\FileName1.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName2,
FILENAME = N'e:\MSSQL\Data\FileName2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName3,
FILENAME = N'e:\MSSQL\Data\FileName3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName4,
FILENAME = N'e:\MSSQL\Data\FileName4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName5,
FILENAME = N'e:\MSSQL\Data\FileName5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName6,
FILENAME = N'e:\MSSQL\Data\FileName6.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName7,
FILENAME = N'e:\MSSQL\Data\FileName7.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName8,
FILENAME = N'e:\MSSQL\Data\FileName8.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName9,
FILENAME = N'e:\MSSQL\Data\FileName9.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName10,
FILENAME = N'e:\MSSQL\Data\FileName10.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName11,
FILENAME = N'e:\MSSQL\Data\FileName11.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName12,
FILENAME = N'e:\MSSQL\Data\FileName12.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName13,
FILENAME = N'e:\MSSQL\Data\FileName13.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName14,
FILENAME = N'e:\MSSQL\Data\FileName14.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName15,
FILENAME = N'e:\MSSQL\Data\FileName15.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName16,
FILENAME = N'e:\MSSQL\Data\FileName16.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName17,
FILENAME = N'e:\MSSQL\Data\FileName17.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName18,
FILENAME = N'e:\MSSQL\Data\FileName18.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName19,
FILENAME = N'e:\MSSQL\Data\FileName19.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName20,
FILENAME = N'e:\MSSQL\Data\FileName20.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName21,
FILENAME = N'e:\MSSQL\Data\FileName21.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName23,
FILENAME = N'e:\MSSQL\Data\FileName23.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName24,
FILENAME = N'e:\MSSQL\Data\FileName24.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName25,
FILENAME = N'e:\MSSQL\Data\FileName25.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName26,
FILENAME = N'e:\MSSQL\Data\FileName26.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName27,
FILENAME = N'e:\MSSQL\Data\FileName27.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName28,
FILENAME = N'e:\MSSQL\Data\FileName28.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName29,
FILENAME = N'e:\MSSQL\Data\FileName29.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName30,
FILENAME = N'e:\MSSQL\Data\FileName30.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName31,
FILENAME = N'e:\MSSQL\Data\FileName31.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName32,
FILENAME = N'e:\MSSQL\Data\FileName32.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)

LOG ON
( NAME = FileName_Log1,
FILENAME = N'e:\MSSQL\Data\FileName_Log1.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName_Log2,
FILENAME = N'e:\MSSQL\Data\FileName_Log2.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO

Now detach the database and use windows explorer to move the data files and log files to a new directory (in this case e:\test\data).

Now use CREATE DATABASE ... FOR ATTACH to re-attach the database:

CREATE DATABASE MultiFile
ON PRIMARY
(FILENAME = N'e:\Test\Data\FileName1.mdf'),
(FILENAME = N'e:\Test\Data\FileName2.ndf'),
(FILENAME = N'e:\Test\Data\FileName3.ndf'),
(FILENAME = N'e:\Test\Data\FileName4.ndf'),
(FILENAME = N'e:\Test\Data\FileName5.ndf'),
(FILENAME = N'e:\Test\Data\FileName6.ndf'),
(FILENAME = N'e:\Test\Data\FileName7.ndf'),
(FILENAME = N'e:\Test\Data\FileName8.ndf'),
(FILENAME = N'e:\Test\Data\FileName9.ndf'),
(FILENAME = N'e:\Test\Data\FileName10.ndf'),
(FILENAME = N'e:\Test\Data\FileName11.ndf'),
(FILENAME = N'e:\Test\Data\FileName12.ndf'),
(FILENAME = N'e:\Test\Data\FileName13.ndf'),
(FILENAME = N'e:\Test\Data\FileName14.ndf'),
(FILENAME = N'e:\Test\Data\FileName15.ndf'),
(FILENAME = N'e:\Test\Data\FileName16.ndf'),
(FILENAME = N'e:\Test\Data\FileName17.ndf'),
(FILENAME = N'e:\Test\Data\FileName18.ndf'),
(FILENAME = N'e:\Test\Data\FileName19.ndf'),
(FILENAME = N'e:\Test\Data\FileName20.ndf'),
(FILENAME = N'e:\Test\Data\FileName21.ndf'),
(FILENAME = N'e:\Test\Data\FileName23.ndf'),
(FILENAME = N'e:\Test\Data\FileName24.ndf'),
(FILENAME = N'e:\Test\Data\FileName25.ndf'),
(FILENAME = N'e:\Test\Data\FileName26.ndf'),
(FILENAME = N'e:\Test\Data\FileName27.ndf'),
(FILENAME = N'e:\Test\Data\FileName28.ndf'),
(FILENAME = N'e:\Test\Data\FileName29.ndf'),
(FILENAME = N'e:\Test\Data\FileName30.ndf'),
(FILENAME = N'e:\Test\Data\FileName31.ndf'),
(FILENAME = N'e:\Test\Data\FileName32.ndf')

LOG ON
(FILENAME = N'e:\Test\Data\FileName_Log1.ldf'),
(FILENAME = N'e:\Test\Data\FileName_Log2.ldf')

FOR ATTACH
GO

um...people...just images...no mpgs?|||Very nice...so it's not an adult site?|||Nope, not an adult site (heehee).

Things went well this weekend. Moved both databases successfully and then re-attached them with the FOR ATTACH option. It was surprisingly painless all considered.

Regards,

hmscott
Very nice...so it's not an adult site?