Default FILESTREAM filegroup is not available in database

#11

On Aug 1, 9:24*pm, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:

Quote:

And I get another error:
FILESTREAM feature is disabled.
Invalid filegroup 'Files' specified.
Default FILESTREAM filegroup is not available in database 'Badb'.
No rows affected.

I would expect those messages from your script if file FILESTREAM featureis
not enabled for the instance. *Ensure FILESTREAM is enabled using the SQL
Configuration Manager tool.

--
Hope this helps.

Dan Guzman
SQL Server MVPhttp://weblogs.sqlteam.com/dang/

"shapper" <mdmo... (AT) gmail (DOT) com> wrote in message

news:b58416d4-c17c-4daa-b3d6-ff0ab8f0624a (AT) k6g2000yqn (DOT) googlegroups.com...
On Aug 1, 7:31 pm, "jacob.relianc... (AT) gmail (DOT) com"

jacob.relianc... (AT) gmail (DOT) com> wrote:
On Aug 1, 11:15 pm, shapper <mdmo... (AT) gmail (DOT) com> wrote:

On Aug 1, 6:23 pm, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net
wrote:

I think Tibor has answered most your questions.

How can I associated a file group named Posts to table Posts and
another one named Slides to table Slides?
Shouldn't I do this?

You can either use the same filegroup or separate ones for the
filestream
data in both tables. Personally, I would use the same (default)
filegroup
unless you have a reason to do otherwise. It might be appropriate to
segregate the filegroup storage if you have different recovery
(read-only
vs. read-write) or performance (fast vs slow disks) requirements.

--
Hope this helps.

Dan Guzman
SQL Server MVPhttp://weblogs.sqlteam.com/dang/

"shapper" <mdmo... (AT) gmail (DOT) com> wrote in message

news:f6c09e17-5f9d-49ef-a1ed-d129170b1ec6 (AT) c14g2000yqm (DOT) googlegroups.com...
On Aug 1, 2:37 pm, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net
wrote:

A least one FILESTREAM filegroup file is needed in order to use
filestream
storage. Also, one of the filestream filegroups needs to be marked
as the
default if you don't specify FILESTREAM ON clause in your CREATE
TABLE.

I added the following:
add filegroup Slides contains filestream;

So Slides is the default file group.

You can add a FILESTREAM filegroup to you database from SQL Server
Management Studio or script.

I am using Script

Script example (the first FILESTREAM filegroup is the default one):

ADD FILEGROUP Bar CONTAINS FILESTREAM;

ALTER DATABASE Foo
ADD FILE ( NAME = N'Bar',
FILENAME = N'C:\Bar' ) TO FILEGROUP [Bar];

I don't understand this. FileGroup will be a folder where the data is
saved, correct?

I have two tables. Posts and Slides, where both ones have a column
named File of type file stream.
How can I associated a file group named Posts to table Posts and
another one named Slides to table Slides?
Shouldn't I do this?

And do I need to specify the path?
Can I define the path as the same as the database file is located?
If not then I will need to change this either using the database onmy
computer or on my hosting server.

Note that you also need a ROWGUIDCOL to your uniqueidentifier
definition.

Should I add this to all my other tables? And shoulc I have clustered
on my IDs?

And don't I also need to do the following for File Stream?

exec [sp_filestream_configure] @enable_level = 3;
go

Hello,

I am using the following:

add filegroup Files contains filestream;

alter database Badb
add file ( name = N'Files', filename = N'c:\Program Files\Microsoft
SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA' )
to filegroup [Files];

create table Slides (
ID uniqueidentifier not null rowguidcol
constraint PK_Slide primary key clustered,
Active bit not null default 0,
[File] varbinary(max) filestream default(0x),
[Name] nvarchar(100) null,
Url nvarchar(400) null
) -- Slides

And I always get the error:
Incorrect syntax near 'filegroup'.

What am I missing?
I checked my code but I can't find the error.

Thank You,
Miguel

Do you have an 'ALTER DATABASE dbname' statement 'ADD FILEGROUP'?

Yes I also used that:

alter database Badb
add filegroup Files contains filestream;

alter database Badb
add file ( name = N'Files', filename = N'c:\Program Files\Microsoft
SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA' )
to filegroup [Files];

And I get another error:
FILESTREAM feature is disabled.
Invalid filegroup 'Files' specified.
Default FILESTREAM filegroup is not available in database 'Badb'.
No rows affected.

Yes, I did that before following:
http://www.sql-server-performance.co...r_2008_p1.aspx

But I was keeping the same error.

Then I tried:
exec sp_configure 'filestream access level', 2
go
reconfigure
go

And that error stopped but I thought this wasn't necessary ...

Now I get another error (this never ends):
FILESTREAM data cannot be placed on an empty filegroup.

This is my entire code:

exec sp_configure 'filestream access level', 2
go
reconfigure
go

alter database TestDB
add filegroup Files contains filestream;

alter database TestDB
add file ( name = N'Files',
filename = N'c:\Program Files\Microsoft SQL Server
\MSSQL10.SQLEXPRESS\MSSQL\DATA' )
to filegroup [Files];

create table Slides (
ID uniqueidentifier not null rowguidcol
constraint PK_Slide primary key clustered,
Active bit not null default 0,
[File] varbinary(max) filestream default(0x),
[Name] nvarchar(100) null,
Url nvarchar(400) null
) filestream_on Files -- Slides

I added "filestream_on Files" at the end as I think it is needed.

I also went to c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS
\MSSQL\DATA and there is not file names Files there.
Shouldn't be one?

Thank You,
Miguel

#12

Quote:

I added "filestream_on Files" at the end as I think it is needed.

The FILESTREAM_ON clause is optional. It is needed only if you want to use
a FILESTREAM filegroup other than the default one.

Quote:

I also went to c:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA
and there is not file names Files there. Shouldn't be one?

The path you specify should be a folder that does not exist (although the
parent folder(s) must exist). The folder is created when the ALTER
DATABASE...ADD FILE statement runs. If the folder already exists, you
should an error stating so.

I successfully ran the following script on my instance. Does this work for
you?

EXEC sp_configure 'filestream access level',2;
RECONFIGURE;
GO

CREATE database Badb;
GO

ALTER DATABASE Badb
ADD FILEGROUP Files CONTAINS FILESTREAM;
GO

ALTER DATABASE Badb
ADD FILE( NAME = N'Files', FILENAME = N'c:\MyFiles' )
TO FILEGROUP [Files];
GO

USE Badb;
GO

CREATE TABLE dbo.Slides (
ID uniqueidentifier NOT NULL ROWGUIDCOL
CONSTRAINT PK_Slide PRIMARY KEY CLUSTERED,
Active bit NOT NULL CONSTRAINT DF_Slides_Active DEFAULT 0,
[File] varbinary(max) FILESTREAM DEFAULT(0x),
[Name] nvarchar(100) NULL,
Url nvarchar(400) NULL
);

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

#13

shapper wrote on 08/01/2009 07:57 ET :

Quote:

Hello,

I am creating a table on a SQL 2008 database as follows:

create table Slides (
ID uniqueidentifier not null
constraint PK_Slide primary key clustered,
[File] varbinary(max) filestream default(0x),
[Name] nvarchar(100) null,
) -- Slides

I get the error:
Default FILESTREAM filegroup is not available in database
"MyDatabase"

How can I solve this problem?

Thanks,
Miguel

A filegroup with FileStream enabled and a database file should be created

first.
Please check the following URL for solution of a similar problem

http://www.kodyaz.com/t-sql/default-...-database.aspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Default FILESTREAM filegroup is not available in database

microsoft.public.sqlserver.server microsoft.public.sqlserver.server

Re: Default FILESTREAM filegroup is not available in database - 08-01-2009 , 04:14 PM

Re: Default FILESTREAM filegroup is not available in database - 08-01-2009 , 04:48 PM

Re: Default FILESTREAM filegroup is not available in database - 07-28-2011 , 08:39 AM