Altering a Database by Adding a Database File
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:\program files\microsoft sql server\mssql\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:\program files\microsoft sql server\mssql\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:\program files\microsoft sql server\mssql\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