Difference between FILESTREAM and set a path for the file

Question

I think I know about the basic of these situations.

1) when you insert a .PDF file into a database, it will be inside the .MDF file:

INSERT INTO normal_table(id ,nome_arquivo,arquivo)
SELECT 1,'File_name',bulkcolumn
FROM OPENROWSET(BULK N'C:\Users\yeah\Desktop\something.pdf',SINGLE_BLOB) AS ImageSource

2)When you create a filestream database, you will be using some folder, inside some driver, to allocate the inserted file, and then I can set the folder to be in another disc with more space:

use  Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'E:\Filestream_patch\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'E:\Filestream_patch\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'E:\Filestream_patch\archlog1.ldf')
GO

CREATE TABLE [dbo].[FS_Table]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[UI] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[FS_Data] [varbinary](max) FILESTREAM NULL
)

Use Archive
GO
INSERT INTO [dbo].[FS_Table] ( UI, FS_Data)
VALUES (
NEWID()
,(SELECT * FROM OPENROWSET(BULK N'C:\Users\yeah\Desktop\something.pdf', SINGLE_BLOB) AS Image001)
);
GO

But what about developers that use something like, set a path to the file, and then use it inside c# code?

CREATE TABLE Example
( id int, file_path varchar(500))

What is the difference between using this path strategy, and filestream? How can I evaluate this situation, to know what can be better for the company?

Answer 1

If you back up the database that hosts your 'Example' table, you backed up the string of characters c:\asohdo\ash\file.pdf (that point to your pdf file) but you didn't backup the actual PDF file.

FILESTREAM data is included in your database backup (unless you specifically exclude it).

Check out the FILESTREAM Overview for more information