Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Exclude all database images from SQL database backup

Posted on 2014-08-15
Medium Priority
Last Modified: 2014-08-27
Hi Guys,

Is there a way to backup a SQL database, excluding all images stored in the database?
Question by:Rupert Eghardt
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40262582
Depends. How are you storing those images? Filestream or large column?
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40263274
There almost certainly is, but it will make the restore/recovery much more involved.
LVL 25

Expert Comment

ID: 40267399
If your images are in tables on a different filegroup you can go from there

But then maintaining integrity and administering backup and restore get more complex as ScottPletcher already pointed out.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Rupert Eghardt
ID: 40274738
The problem is that the database was designed / setup by a 3rd party developer, who is currently unavailable.
I was hoping that there is a simple selection in SQL to "exclude" images during backup.
I have no idea which of the more than 100 tables are storing the images.
Which data-types should I look for?
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40275619
First you can check if Filestream is enabled to that SQL Server instance. In SSMS, right-click on instance name, go to Properties / Advanced and check if "Filestream Access Level" is disabled.

If is not means that your files are stored in the file system and not in the tables. So you just need to check which file it's designated for filestream.

But if "Filestream Access Level" is disabled, means that your database is using BLOB for storing images. There's a easy and fast way to check the tables size. You just need to right-click on the database name, choose Reports / Standard Reports / Disk usage by top tables. Then you'll have the tables that are consuming more space and check for each of those tables for BLOB columns (typically Binary or VarBinary data fields).
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 40277019
If is not means that your files are stored in the file system and not in the tables.
Not to quibble, but the fact that it is not disabled does not imply that.  FILESTREAM could be enabled just not used.

But I suspect that if the author is asking the question, it is because the images are in fact stored in the database and the maintenance is becoming a nightmare.  I further suspect that is the case when they say that the "database was designed / setup by a 3rd party developer, who is currently unavailable".  If I had designed the database that way, I would disappear, too.  :)

But your point, re the reports is spot on and should give an idea as the problem tables.  But again if I was to guess, I suspect the 3rd party developer MIA probably used the deprecated image data type.

Once they have identified the tables, they should think about FILEGROUPS and if they have the Enterprise Edition even partitioning the tables, as it could well be that they can then segregate old data into read-only FILEGROUPS that can be backed up less often.  But certainly this does add a certain amount of complexity to any RESTORE plan and should be tested carefully.
LVL 53

Accepted Solution

Vitor Montalvão earned 1000 total points
ID: 40278331
FILESTREAM could be enabled just not used.

Sure, but since it's disabled by default I just assuming that if someone enable it it's because he need it and going to use it. Anyway he just need to check if there's some datafile for filestream. If not then he can put option out and check for others options.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40279977
A more rigorous test would be something like this:
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE   c.is_filestream = 1

Open in new window

This will display all the tables and columns that have a FILESTREAM attribute.  My guess is that there are none.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question