Restore database excluding datafile filestream data

Question

Restore database excluding datafile filestream data

Alen Cappelletti 1,047

Hi all,
I would like to know if it is possible to implement such an action...

A got a big db with 3 data file:

.mdf (system only)
.ndf (user data)
.log (as the name))
- 900 Gb of steam data (another data file dedicated).

I just read this post: https://learn.microsoft.com/en-us/answers/questions/794391/export-copy-database-without-filestream-data
"A backup/copy is always an exact 1:1 copy with anything included in the database, you can not make a partial backup/copy of a database". OK clear isn't possible."

But partitioning on a table of type Filestream is it possible? I never tried...
I would like to create 4 datafiles of type stream data....
cold, warm, hot, actual (example) for restoring not all the database but only the filegroup that I need, in my case last one (actual).

My alternative is to feed a simple active data window... moving records in and out of the Filestream folder directly on my filesystem obviously on another database dedicated for DEV.

I used this switching technique with a partitioning normal table using a partition function.

Thanks Alen.

Anonymous

2023-06-27T08:49:40.0366667+00:00

Hi @Alen Cappelletti

You can take a look at the answer below. If it can help you, please accept it.

Accepted answer

0 additional answers

Your answer

Anonymous

2023-06-27T08:49:40.0366667+00:00

Hi @Alen Cappelletti

You can take a look at the answer below. If it can help you, please accept it.

Answer 1

Erland Sommarskog 121.7K MVP Volunteer Moderator

When you restore a database, you can opt to only restore certain files or filegroups. You can restore a filegroup, apply logs, then apply the last log WITH RECOVERY, so that the database is partly accessible. That is, you will not be able to access tables on the filegroups you have not restored, and there can be issues if there are indexes on the non-restored filegroup as well.

Thus, in your case, you should be able to restore only the PRIMARY filegroup and bring it online. The filestream data is its own filegroup, so it is not included. But since filestream table itself, is located in this filegroup, I guess you should be able to access the other column in this table.

I need to add the caveat that I have not actually tested this.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Erland Sommarskog 121.7K MVP Volunteer Moderator

There were some silly errors in my original script. Here is one that works from start to end without any error, beside the one that is expected.

USE tempdb
go
CREATE DATABASE filestreamtest ON (NAME = 'filestreamtest', FILENAME = 'S:\MSSQL\TJUTVÅ\filestreamtest.mdf'),
FILEGROUP filestreamdata CONTAINS FILESTREAM (NAME = 'filestreamtestdir', FILENAME = 'S:\MSSQL\TJUTVÅ\filstreamtestdir')
LOG ON (NAME = 'filestreamtest_log', FILENAME = 'S:\MSSQL\TJUTVÅ\filestreamtest.ldf')
go
ALTER DATABASE filestreamtest SET RECOVERY FULL
go
USE filestreamtest
go
CREATE TABLE normaltable (a int NOT NULL, b sysname NOT NULL, CONSTRAINT pk_normal PRIMARY KEY (a))
INSERT normaltable (a, b) SELECT object_id, name FROM sys.objects
go
CREATE TABLE filestreamtable 
   (id int NOT NULL,
    guid uniqueidentifier NOT NULL ROWGUIDCOL,
    filename sysname NOT NULL,
    filestream varbinary(MAX) FILESTREAM NULL
    CONSTRAINT pk_filestreamtable PRIMARY KEY (id),
    CONSTRAINT u_guid UNIQUE( guid)
)
go
INSERT filestreamtable(id, guid, filename, filestream)
  VALUES(1, newid(), 'slask.txt', convert(varbinary(MAX), 'This is a test text'))
go
BACKUP DATABASE filestreamtest TO DISK = 'C:\temp\filestreamtest.bak' WITH COMPRESSION, INIT
go
USE tempdb
go
DROP DATABASE filestreamtest
go
RESTORE DATABASE filestreamtest FILEGROUP = 'PRIMARY' FROM DISK = 'C:\temp\filestreamtest.bak' WITH PARTIAL, RECOVERY
go
USE filestreamtest
go
SELECT * FROM normaltable
go
SELECT id, guid, filename FROM filestreamtable -- Works
go
SELECT * FROM filestreamtable  -- Fails as expected.
go
USE tempdb
go
RESTORE DATABASE filestreamtest FILEGROUP = 'filestreamdata' FROM DISK = 'C:\temp\filestreamtest.bak' WITH RECOVERY  -- Fails, but we would like it to succeed.
go
USE filestreamtest
go
SELECT * FROM filestreamtable   -- Now succeeds!
go
USE tempdb
go
DROP DATABASE filestreamtest

Avinash Rama 1 Reputation point

2024-01-11T06:03:16.0366667+00:00

Hi,
Do you perhaps know how to bring the FILESTREAM filegroup online after restoring the PRIMARY filegroup from a file/filegroup backup? In other words, we only backed up the "PRIMARY" filegroup. In your example, you are creating a full backup(Including filestream). After the PRIMARY filegroup restore, Filestream filegroup is in a "RECOVERY_PENDING" or "RESTORING" state, and i would like to bring this to an "ONLINE" state. Main reason is that we still backup this DB as per our policy but backups are failing due to the FILESTREAM filegroup not being in an Online state.
Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator

2024-01-11T21:57:39.3266667+00:00

General house rules: don't piggyback on old posts, but ask a new Question describing your problem from start to end. Quick answer: to bring the FILESTREAM filegroup online, you need to restore the backup for the that filegroup. If you don't have a backup, you don't have a backup and that's the end of the line.

Share via

Restore database excluding datafile filestream data

0 additional answers

Your answer