SQL Server Backup - Filestream?

[Sebi2016]

Hi guys,

we are using Veeam Backup 9 since a few weeks for backing up a bunch of SQL Servers. As everything is working as expected we would like to add more SQL Servers. One of this candidate has a database with filestream option enabled. (~90GB in LDF/MDF, ~750GB in ~290000 filestream files). How should we add this server correctly?

We currently do the backup with SQL Server tools and all database content/filestream content is exported into one .bak file at the end.

Any help would be greatly appreciated

Sebastian

[PTide]

Hi and welcome to the community.

These articles might be helpful: 1, 2, 3. Also please use forum search.

Thank you.

[Sebi2016]

Hi,

thanks for your reply. I've already found this articles but it don't fit to our situation.
I'm not talking about Sharepoint.

We have one database which contains parts in filestream files. The question is how we can backup this database with Veeam (Kind of best practices / whitepapers would be nice).

Best regards
Sebastian

[PTide]

I'm not talking about Sharepoint.

Though the major topic of the article is "VSS wait timeout" it is related to filestream enabled database backup:

Certain configurations (such as MS SQL Server with FILESTREAM) or third-party VSS writers may require significant time for preparation. The timeout can be extended to several hours.

Other links also contain some useful info about staging server setup which is required for some special restore operations.

We have one database which contains parts in filestream files. The question is how we can backup this database with Veeam (Kind of best practices / whitepapers would be nice).

Please take a look at this paper.

Thank you.

[pawel]

The link above seems to be dead since this thread is a little old. But I have the same situation that I think OP had. I have a 10GB database that stores lots of PDF files in it and has filestream enabled. When I export the database using the SQL explorer all the image files get restored as individual files. Like this:

Code: Select all

 Directory of E:\sql test

08/18/2017  03:07 PM    <DIR>          .
08/18/2017  03:07 PM    <DIR>          ..
08/18/2017  02:59 PM    <DIR>          DOC_IMG_BOUND_FILES_fs
08/18/2017  03:03 PM    <DIR>          DOC_IMG_BOUND_THUMBS_fs
08/18/2017  03:06 PM    <DIR>          DOC_IMG_PAGE_FILES_fs
08/18/2017  03:07 PM    <DIR>          DOC_IMG_PAGE_THUMBS_fs
08/10/2017  12:42 AM     1,132,920,832 Foundation_Data.mdf
08/10/2017  12:42 AM       204,079,104 Foundation_Log.ldf
               2 File(s)  1,336,999,936 bytes
               6 Dir(s)  89,447,690,240 bytes free

When I try to attach I get an error as the files are not in the same subfolder as they are on the original server, this is the error:

Code: Select all

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'localhost\symfony'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Unable to open the physical file "D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_BOUND_FILES_fs". Operating system error 2: "2(The system cannot find the file specified.)".
A file activation error occurred. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_BOUND_FILES_fs' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Unable to open the physical file "D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_BOUND_THUMBS_fs". Operating system error 2: "2(The system cannot find the file specified.)".
A file activation error occurred. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_BOUND_THUMBS_fs' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Unable to open the physical file "D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_PAGE_FILES_fs". Operating system error 2: "2(The system cannot find the file specified.)".
A file activation error occurred. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_PAGE_FILES_fs' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Unable to open the physical file "D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_PAGE_THUMBS_fs". Operating system error 2: "2(The system cannot find the file specified.)".
A file activation error occurred. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL11.CAS_FOUNDATION\MSSQL\Data\Foundation\DOC_IMG_PAGE_THUMBS_fs' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Could not open new database 'Foundation'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3156&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

It's looking for the files in the original location on the original server, that's a problem since we have a new path on the new server. I am just testing various restore scenarios in which I can restore from Veeam directly, maybe this isn't the best way to do it? I haven't tried creating a manual path and copying things there as that seems "hacky".

The system requirements in the documentation mention that Veeam will use a staging server on the localhost which MSSQL is installed on. So I think I meet the requirements there.