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 to attach. If you have not completed the section Detach a database, complete the following steps.

When we attach the database, we will set QUERYWORKS\srogers 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.

We will first 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:\DATA\TestDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FG1]
( NAME = N'data1', FILENAME = N'C:\DATA\data1.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )...