Exclude all database images from SQL database backup

Hi Guys,

Is there a way to backup a SQL database, excluding all images stored in the database?
Rupert EghardtProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Depends. How are you storing those images? Filestream or large column?
Scott PletcherSenior DBACommented:
There almost certainly is, but it will make the restore/recovery much more involved.
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rupert EghardtProgrammerAuthor Commented:
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
Anthony PerkinsCommented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.