MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1854

 The article didn't prove to me that everything was backed up.  (But it's early in the am, so maybe my thinking cap isn't on just right...)

When the backup was restored on the same machine, the image files would still be in their original places, so it could just be finding the files it needs by accident.

If the database is restored on a different, stand-alone machine (so images on a shared network drive wouldn't be found), what happens?  Are the images restored also?  Do the various directories they are placed in get created in the file system?  How does that work if the source machine was installed on D: and the restored machine used C:?  Or are they images just not included in the backup?

If the files are just not included, how does a dba know what directories and files need to be backed up?  What strategies/tools can they use to easily get all the files backed up in a manner that makes it easy to restore them to all the crazy, uncontrolled places a developer could have loaded them from?  Or are images limited to a few special directories that the dba creates?

Thanks!

If you are restoring the FULL backup of a FILESTREAM enabled database on a different server, you will be able to see all the images files along with other content within the database once the database is successfully restored on the new machine. However, prior to the restore please make sure that the FILESTREAM feature is enabled on “Server B”.

If there are frequent changes happening on the FILESTEAM enabled database then it would be a good practice to execute CHECKPOINT prior to the backup of database as this will EXPLICIT call FILESTREAM Garbage Collector process.  

Hope this helps!

hi,

It is a very nice post. i have tried taking the backup of database containing filestream and it works fine. I have tried restoring it on other server also and that works too.

Now, i am facing another problem. Our database is big in size (approx. 320 GB) and it takes time to backup and restore it. Therefore, the client wants us to suggest some technique to reduce the time.

I have tried piecemeal restore which allows you to backup and restore individual filegroups in the database. It works fine with all the other filegroups except for Filestream ones. I am able to take backup and not able to restore it.

Do u have any idea??

Regards,

Prashant.

Prashant,

We will pose the question to some SQL Server vendors in the community to see if they can be of any assistance to reduce the backup and recovery time.

Thank you,
The MSSQLTips Team

Hi There,

Idera's SQL Safe V6.0 (currently in late stage beta, GA on 2 November 2009) handles both backup and restore of databases including FileStream data beautifully.

The product, with its new compression algorithms gleaned directly from world renowned compression experts and patented IntelliCompress technology, will compress SQL Server database backups such as these to a very high ratio indeed (typically 85%+) whilst at the same time dramatically reducing both backup and restore times.

The beta can be accessed until 30 October here http://www.idera.com/Promo/SQL-safe-6-SQL-vdb-Beta/

Brett Hawton

Idera Product Designer 

Generally backup and restore of FILESTREAM enabled database that too which has large number of FILESTREAM files will be slower than a backup or restore of an SQL Server database of an equivalent size. This happens because of the extra overhead of backing up each NTFS file and this overhead becomes move noticeable when the FILESTREAM files are very small in size (less than 1 MB).

May I know out of 320 GB, how much space is currently used for FILESTREAM data storage? And also let me know how many files are stored and there average size?

I am assuming that you have millions of FILESTREAM files and there could be many files which are of size below 1 MB. Ideally FILESTREAM should be used when you are storing file greater than 1 MB.

You can optimize NTFS performance by turning off the below two processes by running it within the command prompt.

FSUTIL BEHAVIOR SET DISABLE8DOT3 1

The above option disables the generation of 8.3 names on all NTFS volumes on the server where FILESTREAM Feature is configured.

FSUTIL BEHAVIOR SET DISABLELASTACCESS 1

The above option will turn off updating the last access time for a file when it is accessed.

Hope this helps!

Hope this helps!

Claire Brooking

Red Gate Software