What happens when you backup a filestream enabled database in SQL Server 2008? According to BOL, your data is backed up as part of the normal backup process.

Does it work? Let’s do a little test. I made a simple AdventureWorks2008 backup like this:

backup database AdventureWorks2008 to disk = ‘c:\sqlbackup\ADW2K8test.bak’

That gave me a single file, 222MB in size. This includes the filestream data, which by default is located in the \Documents folder below your default data folder in SQL Server 2008.

I can create two new folders. I created a “test” folder below my data directory on the test machine, and then a “FilestreamDocs” folder below that.

I then issued this restore:

RESTORE DATABASE [ADW2k8Test] 
 FROM  DISK = N'C:\SQLBackup\adw2008.bak' 
 WITH  FILE = 1,  
 MOVE N'AdventureWorks2008_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test\adw2008.mdf',
 MOVE N'AdventureWorks2008_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test\adw2k8.ldf',  
 MOVE N'FileStreamDocuments' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test\filestreamdocs',  
 NOUNLOAD,  REPLACE,  STATS = 10
GO

That completed, and I had a fully functional database that included my filestream data.