Exclude all database images from SQL database backup

Posted on 2014-08-15
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 44

    Expert Comment

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

    Expert Comment

    There almost certainly is, but it will make the restore/recovery much more involved.
    LVL 25

    Expert Comment

    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.

    Author Comment

    by:Rupert Eghardt
    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 44

    Expert Comment

    by:Vitor Montalvão
    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
    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 44

    Accepted Solution

    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
    A more rigorous test would be something like this:
    SELECT  *
    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Systems talking to each other 5 80
    Cluster index creation - SQL Server 4 32
    SQL Server 2008 Error 7 36
    STDEVP in SQL 2 12
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now