Export (0) Print
Expand All

Using Filegroups and Files to Store Data

Data files are used to store database files. The data files are subdivided into file groups. The Database object has a FileGroups property that references a FileGroupCollection object. Each FileGroup object in that collection has a Files property. This property refers to a DataFileCollection collection that contains all the data files that belong to the database. A file group is principally used to group files together that are used to store a database object. One reason for spreading a database object over several files is that it can improve performance, especially if the files are stored on different disk drives.

Every database that is created automatically has a file group named "Primary" and a data file with the same name as the database. Additional files and groups can be added to the collections.

For the following code examples, you will have to select the programming environment, programming template and the programming language to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET and How to: Create a Visual C# SMO Project in Visual Studio .NET.

The primary file group and data file are created automatically with default property values. The code example specifies some property values that you can use. Otherwise, the default property values are used.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a FileGroup object called SECONDARY on the database.
Dim fg1 As FileGroup
fg1 = New FileGroup(db, "SECONDARY")
'Call the Create method to create the file group on the instance of SQL Server.
fg1.Create()
'Define a DataFile object on the file group and set the FileName property.
Dim df1 As DataFile
df1 = New DataFile(fg1, "datafile1")
df1.FileName = "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\datafile2.ndf"
'Call the Create method to create the data file on the instance of SQL Server.
df1.Create()


The primary file group and data file are created automatically with default property values. The code example specifies some property values that you can use. Otherwise, the default property values are used.

{ 
Server srv = default(Server); 
srv = new Server(); 
//Reference the AdventureWorks database. 
Database db = default(Database); 
db = srv.Databases("AdventureWorks"); 
//Define a FileGroup object called SECONDARY on the database. 
FileGroup fg1 = default(FileGroup); 
fg1 = new FileGroup(db, "SECONDARY"); 
//Call the Create method to create the file group on the instance of SQL Server. 
fg1.Create(); 
//Define a DataFile object on the file group and set the FileName property. 
DataFile df1 = default(DataFile); 
df1 = new DataFile(fg1, "datafile1"); 
df1.FileName = "c:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\datafile2.ndf"; 
//Call the Create method to create the data file on the instance of SQL Server. 
df1.Create(); 
}

The code example creates a LogFile object, changes one of the properties, and then removes it from the database.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a LogFile object and set the database, name, and file name properties in the constructor.
Dim lf1 As LogFile
lf1 = New LogFile(db, "logfile1", "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\logfile1.ldf")
'Set the file growth to 6%.
lf1.GrowthType = FileGrowthType.Percent
lf1.Growth = 6
'Run the Create method to create the log file on the instance of SQL Server.
lf1.Create()
'Alter the growth percentage.
lf1.Growth = 7
lf1.Alter()
'Remove the log file.
lf1.Drop()


The code example creates a LogFile object, changes one of the properties, and then removes it from the database.

{
//Connect to the local, default instance of SQL Server. 
Server srv = default(Server); 
srv = new Server(); 
//Reference the AdventureWorks database. 
Database db = default(Database); 
db = srv.Databases("AdventureWorks"); 
//Define a LogFile object and set the database, name, and file name properties in the constructor. 
LogFile lf1 = default(LogFile); 
lf1 = new LogFile(db, "logfile1", "c:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\logfile1.ldf"); 
//Set the file growth to 6%. 
lf1.GrowthType = FileGrowthType.Percent; 
lf1.Growth = 6; 
//Run the Create method to create the log file on the instance of SQL Server. 
lf1.Create(); 
//Alter the growth percentage. 
lf1.Growth = 7; 
lf1.Alter(); 
//Remove the log file. 
lf1.Drop(); 
} 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft