SQL Server Backup - Filestream?

Availability for the Always-On Enterprise

SQL Server Backup - Filestream?

Veeam Logoby Sebi2016 » Mon Feb 29, 2016 2:16 pm

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
Sebi2016
Lurker
 
Posts: 2
Liked: never
Joined: Mon Feb 29, 2016 2:02 pm

Re: SQL Server Backup - Filestream?

Veeam Logoby PTide » Mon Feb 29, 2016 3:05 pm

Hi and welcome to the community.

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

Thank you.
PTide
Veeam Software
 
Posts: 3140
Liked: 262 times
Joined: Tue May 19, 2015 1:46 pm

Re: SQL Server Backup - Filestream?

Veeam Logoby Sebi2016 » Tue Mar 01, 2016 7:46 am

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
Sebi2016
Lurker
 
Posts: 2
Liked: never
Joined: Mon Feb 29, 2016 2:02 pm

Re: SQL Server Backup - Filestream?

Veeam Logoby PTide » Tue Mar 01, 2016 8:19 am

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.
PTide
Veeam Software
 
Posts: 3140
Liked: 262 times
Joined: Tue May 19, 2015 1:46 pm

Re: SQL Server Backup - Filestream?

Veeam Logoby pawel » Fri Aug 18, 2017 9:27 pm

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.
pawel
Enthusiast
 
Posts: 34
Liked: 3 times
Joined: Thu Nov 20, 2014 10:54 pm


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google [Bot], Yahoo [Bot] and 36 guests