Sql-server – Create File Stream Enabled Database in Network File (NAS Server)

For SQL Server 2005+ You can use the following:

SELECT FILE_ID, type, type_desc, 
CAST( CAST( (CASE WHEN size < 128 THEN 128 ELSE size END) * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' AS SIZE, 
CASE max_size WHEN -1 THEN 'UNLIMITED' ELSE CAST( CAST( max_size * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' END  AS MAXSIZE,
CASE is_percent_growth
    WHEN 0 THEN CAST( CAST( growth * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB'
    WHEN 1 THEN CAST( growth AS VARCHAR(30) ) + '%'
END AS FILEGROWTH
FROM sys.master_files
WHERE database_id = DB_ID('model')
GO

This will retrieve File definitions for Model Database.
Later on You can use it either in CREATE DATABASE or ALTER DATABASE.

SSMS actually uses SMO to retrieve these parameters and then creates a Script which looks like:

CREATE DATABASE [aaa] ON  PRIMARY 
( NAME = N'aaa', FILENAME = N'D:\Data\aaa.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'aaa_log', FILENAME = N'D:\Logs\aaa_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

If Your Application is creating the Database and You can't modify the application -
then You have to use ALTER DATABASE after creation is done:

ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa', MAXSIZE = 102400KB )
GO
ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa_log', FILEGROWTH = 10240KB )
GO

This requires at least ALTER DATABASE Permission
(implied by ALTER ANY DATABASE Server Permission)
Obviously, You can generate that script using the first code sample (FROM sys.master_files).

I wouldn't use sysaltfiles for SQL Server 2008R2 because it is Deprecated.

Good luck,
Roi

Database schema for all objects is stored in the PRIMARY filegroup although user data may optionally be stored in a different filegroup. A piecemeal of individual filegroups is not intended to be used to create a new database, but rather to restore critical data before less frequently used data. That technique allows a database to be partial available for use during a restore sequence before the entire restore is complete.

if no, what is the purpose of the file groups then ?

In addition to piecemeal restores, user-defined filegroups allow you to control placement of individual tables, indexes, and partitions. This allows the underlying files to be placed on different underlying storage devices such as in a scenario where isolated storage provides a performance improvement (e.g. segregate random and sequential access) or with tiered storage (e.g. archive data on slower, less expensive media).