使用文件组和文件存储数据

数据文件可用于存储数据库文件。 数据文件可细分为文件组。 Database 对象具有 FileGroups 属性，该属性引用 FileGroupCollection 对象。 该集合中的每个 FileGroup 对象都具有 Files 属性。 此属性引用 DataFileCollection 集合，该集合包含属于数据库的所有数据文件。 文件组主要用于将用于存储数据库对象的文件组合在起来。 将一个数据库对象分布到几个文件上的一个原因是，它可以提高性能，尤其是在文件存储在不同磁盘驱动器上时。

自动创建的每个数据库都具有一个名为“Primary”的文件组和一个与数据库同名的数据文件。 其他文件和组可以添加到集合中。

示例

对于下列代码示例，您必须选择编程环境、编程模板和编程语言才能创建应用程序。 有关详细信息，请参阅在 Visual Studio .NET 中创建 Visual Basic SMO 项目和在 Visual Studio .NET 中创建 Visual C# SMO 项目。

在 Visual Basic 中将 FileGroups 和 DataFiles 添加到数据库

主文件组和数据文件将自动使用默认属性值创建。 代码示例指定了一些可以使用的属性值。 否则，将使用默认属性值。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'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()

在 Visual C# 中将 FileGroups 和 DataFiles 添加到数据库

主文件组和数据文件将自动使用默认属性值创建。 代码示例指定了一些可以使用的属性值。 否则，将使用默认属性值。

{
            Server srv = new Server();
            //Reference the AdventureWorks2012 database. 
            Database db = default(Database);
            db = srv.Databases["AdventureWorks2012"];
            //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();
        }

在 PowerShell 中将 FileGroups 和 DataFiles 添加到数据库

主文件组和数据文件将自动使用默认属性值创建。 代码示例指定了一些可以使用的属性值。 否则，将使用默认属性值。

# Set the path context to the local, default instance of SQL Server.
CD \sql\localhost\default\Databases\

#And the database object corresponding to AdventureWorks2012.
$db = get-item AdventureWorks2012

#Create a new filegroup
$fg1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Filegroup -argumentlist $db, "SECONDARY"
$fg1.Create()

#Define a DataFile object on the file group and set the FileName property. 
$df1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.DataFile -argumentlist $fg1, "datafile1"

#Make sure to have a directory created to hold the designated data file
$df1.FileName = "c:\\TestData\\datafile2.ndf"

#Call the Create method to create the data file on the instance of SQL Server. 
$df1.Create()

在 Visual Basic 中创建、更改和删除日志文件

代码示例将创建 LogFile 对象，更改其中一个属性，然后将其从数据库中删除。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'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()

在 Visual C# 中创建、更改和删除日志文件

代码示例将创建 LogFile 对象，更改其中一个属性，然后将其从数据库中删除。

//Connect to the local, default instance of SQL Server. 
            Server srv = new Server();
            //Reference the AdventureWorks2012 database. 
            Database db = default(Database);
            db = srv.Databases["AdventureWorks2012"];
            //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.10_50.MSSQLSERVER\\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();
  

在 PowerShell 中创建、更改和删除日志文件

代码示例将创建 LogFile 对象，更改其中一个属性，然后将其从数据库中删除。

#Load the assembly containing the enums used in this example
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")

# Set the path context to the local, default instance of SQL Server.
CD \sql\localhost\default\Databases\

#And the database object corresponding to AdventureWorks2012
$db = get-item AdventureWorks2012

#Create a filegroup
$fg1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Filegroup -argumentlist $db, "Secondary"

#Call the Create method to create the file group on the instance of SQL Server. 
$fg1.Create()

#Define a LogFile object on the file group and set the FileName property. 
$lf1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.LogFile -argumentlist $db, "LogFile2"

#Set a location for it - make sure the directory exists
$lf1.FileName = "logfile1", "c:\\Program Files\\Microsoft SQL Server\\MSSQL.10_50.MSSQLSERVER\\MSSQL\\Data\\logfile1.ldf"

#Set file growth to 6%
$lf1.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::Percent
$lf1.Growth = 6.0

#Call the Create method to create the data file on the instance of SQL Server. 
$lf1.Create()

#Alter a value and drop the log file
$lf1.Growth = 7.0
$lf1.Alter()
$lf1.Drop()
  

请参阅

参考

FileGroup

概念

数据库文件和文件组