Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We are going to start using the sql 2008 filestream and going through the instruction setting it up. One of the items that we do is have everything in a single sql script that can be run multiple times without corrupting the data or executing unnecessary steps.

So far have created the file stream group and now want to define the filestream data container with the following statement

ALTER DATABASE Production ADD FILE (
       NAME = FSGroup1File,
       FILENAME = 'F:\Production\FSDATA')
TO FILEGROUP FileStreamGroup1;
GO

Is there any way that I can check to see if this has already been done? i.e.

if (select COUNT(*) from sys.????? where name = '?????')=0
ALTER DATABASE Production ADD FILE (
       NAME = FSGroup1File,
       FILENAME = 'F:\Production\FSDATA')
TO FILEGROUP FileStreamGroup1;
GO

Sorry if this is a basic question but am bit of a novice when it comes to DB.

Thanks.

share|improve this question

2 Answers 2

up vote 1 down vote accepted
IF NOT EXISTS (select 1 from sys.database_files where name = 'LogicalFileName')
     ALTER DATABASE...

Edit: Corrected view name to match suggestion by Nick Kavadias.

share|improve this answer
    
+1 your answer is now correct! –  Nick Kavadias Oct 28 '09 at 13:02
    
thanks this helped out a ton –  AndrewB Oct 28 '09 at 14:35

You can use sys.sysfiles as NYSystemsAnalyst has suggested. although this is compatibility view for SQL Server 2000. The preferred system views to use would be either:

  • sys.database_files - This view displays only the files in the current db context.
  • sys.master_files - This view displays all files for all databases.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.