Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: 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,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    128 files?

    Dude...how big is this thing?

    what's the hardware config?

    No I haven't done this....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    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

    Quote Originally Posted by Brett Kaiser
    128 files?

    Dude...how big is this thing?

    what's the hardware config?

    No I haven't done this....
    Have you hugged your backup today?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    um...people...just images...no mpgs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    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:
    Code:
    -- =============================================
    -- 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:
    Code:
    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
    Quote Originally Posted by Brett Kaiser
    um...people...just images...no mpgs?
    Have you hugged your backup today?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very nice....so it's not an adult site?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    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
    Quote Originally Posted by Brett Kaiser
    Very nice....so it's not an adult site?
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •