sql server - How to complete remove filestream and all attached files -

sql server - How to complete remove filestream and all attached files -

i have tried filestream feature mssql (2008r2 info center) on local database, experiment. real database running on server. have setup whole filestream, using query:

/* create filestream , filestream table */ utilize [master] go alter database senonew add together filegroup [filestream] contains filestream go alter database senonew add together file ( name = 'fssenonew', filename = 'c:\program files\microsoft sql server\mssql10_50.sqlexpress\mssql\data\senonew.ndf' ) filegroup [filestream] go utilize [senonew] go create table filestore( fileid int primary key, rowid uniqueidentifier rowguidcol not null unique default newsequentialid(), filedescr nvarchar(max), fileindex varbinary(max) filestream null) go

and experimenting adding few files deleting them.

now since meant experiment, want rid of it. i'm using local server development of database used on real server, i'm creating backup's on local server restore on real server, gets updated (software in development, database construction changes much info , need total restore real server, software beingness tested on).

after hours of searching, couldn't find on problem.

i understand need to:

remove database table storing filestream information i need remove file of filestream remove filegroup

so i'm using query rid of set in first place:

/* drop filestream table , filegroup */ utilize senonew drop table filestore go alter database senonew remove file fssenonew alter database senonew remove filegroup [filestream] go

so should , completes without error well. when come in filegroups, files , file location, see removed:

but when backup of local database (which include deleted filestream, file path , filegroup) , seek restore server it, errors.

sql create backup:

/* create backup of database within currect connection */ declare @filename2 nvarchar(250) select @filename2 = (select 'c:\seno backup\' + convert(nvarchar(200),getdate(),112) + ' senonew.bak') backup database senonew disk=@filename2 go

then restore on server:

/* create restore of database within real server connection */ utilize master alter database senonew set offline rollback immediate; declare @filename2 nvarchar(250) select @filename2 = (select '' + convert(nvarchar(200),getdate(),112) + ' senonew.bak') restore database senonew disk = @filename2 alter database senonew set online rollback immediate;

i next error:

*(msg 5121, level 16, state 2, line 7 path specified "c:\program files\microsoft sql server\mssql10_50.sqlexpress\mssql\data\senonew.ndf" not in valid directory.

msg 3156, level 16, state 3, line 7 file 'fssenonew' cannot restored 'c:\program files\microsoft sql server\mssql10_50.sqlexpress\mssql\data\senonew.ndf'. utilize move identify valid location file.

msg 3119, level 16, state 1, line 7 problems identified while planning restore statement. previous messages provide details.

msg 3013, level 16, state 1, line 7 restore database terminating abnormally. )*

i deleted .ndf filestream location, why specified path? also, why fssenonew trying restore? can't head around it. there paths internally need delete?

you can check:

select * senonew.sys.data_spaces name = 'filestream'

it should homecoming 0 rows.

there procedure remove filestream features sql server 2008 database :

alter table filestore drop column fileindex go alter table filestore set (filestream_on="null") go alter database senonew remove file fssenonew go alter database senonew remove filegroup [filestream] go

as described in this article. steps did should same thing.

your problem strange, suggest seek using next

use senonew exec sp_help exec sp_helpfile exec sp_helpfilegroup

you may find suspicious there table using filegroup.

i have done steps describe , cannot reproduce problem. check how restore database screen looks like.

sql-server filestream