Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've corrupted the files related to FILESTREAM in a database.

The .mdf and .ldf files are still intact, but when I try to get the database online, it complains, saying that the files related to FILESTREAM are incorrect.

I don't care about the data stored in FILESTREAM, but I do care about the other data. Can I get it back from .mdf and .ldf files? How?

When I execute:

sp_attach_db @dbname = 'Demo',
             @filename1 = N'<File path>.mdf',
             @filename2 = N'<File path>.ldf'

the response is:

Msg 5120, Level 16, State 105, Line 1
Unable to open the physical file "<Location>". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5105, Level 16, State 14, Line 1
A file activation error occurred. The physical file name '<Location>' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 1
share|improve this question

1 Answer 1

up vote 5 down vote accepted

I finally found how to do it. I've found some very old backups. The data changed meanwhile, and it was important to have an up-to-date version of this data, which meant that it wasn't possible to just restore the old backup. Instead, I did the following, thanks to Michael Eklöf on ServerFault:

  1. Copy the current .mdf and .ldf files.

  2. List the filegroups of the old backup:

    use [master]
    go
    
    restore filelistonly
    from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
    go
    
  3. Restore the database from the old backup, with the names of filegroups from the previous result:

    restore database [Hello]
    from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
    with move 'Hello' to 'D:\Database\Hello.mdf',
    move 'Hello_log' to 'D:\Database\Hello_log.ldf',
    move 'Hello_files' to 'D:\Database\Hello_files'
    go
    
  4. Set to offline the filegroup (in my case, the FILESTREAM) which is missing in the new version of the database:

    alter database [Hello]
    modify file (name = 'Hello_files', offline)
    go
    
  5. Take the database offline.

  6. Replace the .mdf and .ldf files created by the server at step 3 by the files copied at step 1.

  7. Take the database online.

There are still consequences of the missing filegroup. Some operations require the database to be complete; on of those critical operations is the backup. In other words, full backup is impossible to do with a database restored through the six steps above.

In order to get the backup ability back, I've copied the structure of the database (through Tasks → Generate Scripts...) excluding the tables which relied on filestream and then copied the data itself.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.