1

I have a mdf and ldf files from a database that in principle have filestream data too.

Since i need to debug an issue related only to the relational tables and the filestream folder is too big I wonder whether there is a way to attach a db only from mdf and ldf and somehow skipping filestream

This is a typical attach query taken from this question:

USE [master]
GO
CREATE DATABASE [AdventureWorks2008] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\AdventureWorks2008_Log.ldf' ),
FILEGROUP [PRIMARY] CONTAINS FILESTREAM DEFAULT 
( NAME = N'Documents', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\Documents' )
FOR ATTACH
GO

Since i do not have the filestream data how can i do?

TT.
  • 15,774
  • 6
  • 47
  • 88
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249

1 Answers1

1

As far as I know there is no way to do this with attaching (since attach requires all files to be present). Also look at this answer.

There is alternative though. If your database is in full recovery mode you can take partial backup without filestream group. You can then restore from that backup and any missing filegroup will be in offline mode and inaccessible. Any query that will try to use objects from missing filegroup will fail.

Sample commands:

BACKUP DATABASE [Demo] 
FILEGROUP = N'PRIMARY'
TO DISK = N'.\MSSQL\Backup\Demo.bak'
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'Demo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [OtherDatabase] 
FILE = N'Demo' FROM  DISK = N'.\MSSQL\Backup\Demo.bak' WITH  FILE = 1,  
MOVE N'Demo' TO N'.\MSSQL\DATA\OtherDatabase.mdf', 
MOVE N'Demo_log' TO N'.\MSSQL\DATA\OtherDatabase_Log.ldf',
MOVE N'FStream' TO N'.\MSSQL\DATA\OtherDatabase_Documents',  NOUNLOAD,  STATS = 10
GO

EDIT:

Some additional helpful links regarding backing and restoring files/filegroups:

EDIT 2:

If your database is in simple recovery mode and you want to take backup of only specific filegroups, you have to make all other filegroups readonly. Then you can use READ_WRITE_FILEGROUPS to backup only R/W filegroups.

Sample commands:

USE [master]
GO
ALTER DATABASE [Demo] MODIFY FILEGROUP [FilestreamGroup] READONLY
GO

BACKUP DATABASE [Demo] READ_WRITE_FILEGROUPS TO  DISK = N'.\MSSQL\Backup\EFCoreDemo.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'Demo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

ALTER DATABASE [Demo] MODIFY FILEGROUP [FilestreamGroup] READ_WRITE
GO

RESTORE DATABASE [OtherDatabase]
FILE = N'Demo' FROM  DISK = N'.\MSSQL\Backup\EFCoreDemo.bak' WITH FILE = 1,  
MOVE N'Demo' TO N'.\MSSQL\DATA\OtherDatabase.mdf', 
MOVE N'Demo_log' TO N'.\MSSQL\DATA\OtherDatabase_Log.ldf',
MOVE N'FStream' TO N'.\MSSQL\DATA\OtherDatabase_Documents',  NOUNLOAD,  STATS = 10
GO
nejcs
  • 1,212
  • 10
  • 14
  • Thanks for the answer to perform the backup i needed to add READ_WRITE_FILEGROUPS too. Moreover on restore it says "The database is using the simple recovery model. It is not possible to restore a subset of the read-write data.", itried to change to FULL recovery mode but the error persists.. Any idea? – UnDiUdin Sep 20 '17 at 08:32
  • I asked this question too https://stackoverflow.com/questions/46324380/is-it-possible-to-create-a-dummy-filestream-filegroup-to-study-mdf-content-only – UnDiUdin Sep 20 '17 at 14:10
  • Before you take backup, database must be in full recovery mode. You do not specify READ_WRITE_FILEGROUPS (this is needed only for simple recovery mode, but does not do what you want if filestream group is R/W). Once you made partial backup, you restore it to the new database, with all file listed. Restore will succeed but since filestream group was not present it will be offline. Added relevant links to post. – nejcs Sep 20 '17 at 17:11
  • Unfortunately this method of swapping .mdf and .ldf files didn't work for me ... all I get is "Recovery Pending" next to the database in SSMS. Sadly I don't have the .HDR FileStream file. I tried putting it in "EMERGENCY" mode that can't execute and commands on it, no tables show, nothing sadly. Any other suggestions? – Rob Ainscough Jun 11 '20 at 23:20