Altering a Database by Adding a Database File (SQL-DMO)

These examples illustrate altering a database by adding data or log maintaining files.

You can create a Microsoft® SQL Server™ database on one or more data-maintaining operating system files. A database log is, similarly, created on one or more operating system files. As a database grows, you can add operating system files to those existing to direct the growth of the database.

When creating a database for SQL Server, database data files are created only in the PRIMARY filegroup. To use filegroups as part of database maintenance tasks such as backup and restore, alter a database to add a filegroup, then add existing or new database files to the filegroup.

Examples

A. Adding a Database Data File

This example illustrates adding a database file to the PRIMARY filegroup of an existing database.

Dim oDatabase As SQLDMO.Database

Dim oDBFile As New SQLDMO.DBFile

' Get the Northwind database. Note: Create and connect of SQLServer

' object used is not illustrated in this example.

Set oDatabase = oSQLServer.Databases("Northwind")

' Define the new data file.

oDBFile.Name = "NorthData2"

oDBFile.PhysicalName = "c:\mssql7\data\northwn2.mdf"

' Specify an initial size and file growth in chunks of fixed size.

oDBFile.Size = 4

oDBFile.FileGrowthType = SQLDMOGrowth_MB

oDBFile.FileGrowth = 1

oDatabase.FileGroups("PRIMARY").DBFiles.Add oDBFile

B. Adding a Database Log File

This example illustrates adding a database transaction log-maintaining operating system file to an existing database.

Dim oDatabase As SQLDMO.Database

Dim oLogFile As New SQLDMO.LogFile

' Get the Northwind database. Note: Create and connect of SQLServer

' object used is not illustrated in this example.

Set oDatabase = oSQLServer.Databases("Northwind")

' Define the database transaction log, setting an initial size.

oLogFile.Name = "NorthLog2"

oLogFile.PhysicalName = "c:\mssql7\data\northwn2.ldf"

oLogFile.Size = 8

oDatabase.TransactionLog.LogFiles.Add oLogFile

C. Adding a Filegroup

This example illustrates adding a filegroup, then using the filegroup when creating a new operating system file used for database data.

Dim oDatabase As SQLDMO.Database

Dim oFileGroup as New SQLDMO.FileGroup

Dim oDBFile As New SQLDMO.DBFile

' Get the Northwind database. Note: Create and connect of SQLServer

' object used is not illustrated in this example.

Set oDatabase = oSQLServer.Databases("Northwind")

' Define the new filegroup.

oFileGroup.Name = "fgNorthwindIdx"

oDatabase.FileGroups.Add oFileGroup

' Define the new data file.

oDBFile.Name = "NorthIdx1"

oDBFile.PhysicalName = "c:\mssql7\data\northix1.mdf"

oDBFile.Size = 2

oDBFile.FileGrowthType = SQLDMOGrowth_MB

oDBFile.FileGrowth = 1

' Alter the database, creating the new file group and data file.

oDatabase.FileGroups("fgNorthwindIdx").DBFiles.Add oDBFile

See Also