Comprehensive data protection for all workloads
Post Reply
Sebi2016
Lurker
Posts: 2
Liked: never
Joined: Feb 29, 2016 2:02 pm
Contact:

SQL Server Backup - Filestream?

Post by 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
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: SQL Server Backup - Filestream?

Post by PTide »

Hi and welcome to the community.

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

Thank you.
Sebi2016
Lurker
Posts: 2
Liked: never
Joined: Feb 29, 2016 2:02 pm
Contact:

Re: SQL Server Backup - Filestream?

Post by 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
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: SQL Server Backup - Filestream?

Post by 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:
helpcenter wrote: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
Enthusiast
Posts: 43
Liked: 3 times
Joined: Nov 20, 2014 10:54 pm
Contact:

Re: SQL Server Backup - Filestream?

Post by 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.
Post Reply

Who is online

Users browsing this forum: No registered users and 142 guests