Attaching a database

In this recipe, we will programmatically attach a database with a primary data file (.mdf), log file (.ldf), and multiple secondary data files (.ndf).

Getting ready

Before we can attach a database, we must have the data files and optional log files attached. If you have not completed the Detaching a database recipe, perform the following steps:

1. When we attach the database, we will set QUERYWORKS\jraynor as the owner. This principal has been created with our development VM. Feel free to replace the appropriate code with a login available with your system.

2. We will create a database called TestDB. Open up SQL Server Management Studio and run the following code:

   IF DB_ID('TestDB') IS NOT NULL
   DROP DATABASE TestDB
   GO
   
   CREATE DATABASE [TestDB]
    CONTAINMENT = NONE
    ON  PRIMARY 
   ( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ), 
    FILEGROUP [FG1] 
   ( NAME = N'data1', FILENAME ...