I am having difficulties attaching DB in a certain environment while the same scenario works in my dev or other production instances.


There is a backup of database files (MDF, NDF, LDF, FileStream) made by backup SW (let's say Veeam). The database is, let's say DB1. The backup was made under the same release of SQL. We've restored the files to a different location and renamed them to become files of DB2 that does not exist.

While DB1 kept online we attempted to attach DB2 files with this code:

(FILENAME = N'D:\Data\DB2.mdf', NAME = N'WhatEver'),
(FILENAME = N'E:\Data\DB2_Img.ndf', NAME = N'WhatEver_Ndf'),
(FILENAME = N'L:\Log\DB2_log.ldf', NAME = N'WhatEver_log'),
(FILENAME = N'F:\FS\DB2_FSData', NAME = N'WhatEver_FS')

and got the error enter image description here

Despite the fact, we've pointed to different files/folder when creating database for attach, MDF file header info that contains paths of DB1 files is prevalent.

Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Standard Edition

The same scenario works well on

Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) Developer Edition

We cannot take DB1 offline/dettach it nor upgrade to higher CU.

Should I consider it a bug in this release of SQL2016 or am I missing something else?

  • 1
    Try specifying the NAME as well as the FILENAME for the files. docs.microsoft.com/en-us/sql/t-sql/statements/… Oct 22 '21 at 17:31
  • Thanks David...I haven't tested it in the given environment but in my dev the NAME atribute is ignored when creating database for attach...which is mentioned in documentation too. What NAME should I use to succeed the task, the desired new one or the original? For desired new one I see no explanation for the error and for the original name I would say this could be the reason for the error...just thinking.
    – jericzech
    Oct 22 '21 at 18:02
  • 2
    NAME is logical name of a file, which doesn't change when moving the database files. Oct 22 '21 at 18:07
  • from the docs: NAME logical_file_name Specifies the logical name for the file. NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses. A FILESTREAM filegroup cannot be named PRIMARY. ... and no, adding any variation of the NAME atribute did not change the error. thanks
    – jericzech
    Oct 22 '21 at 18:44

I took DB1 offline and run the same script for attaching DB2 mentioned above. The script succeeded with a very dangerous file layout afterward:

  • MFD, NDF, LDF files belong to the correct DB2 database
  • FILESTREAM folder belongs to the wrong DB1 database

If there is nothing wrong with the code that I used (please, let me know if there is) the given release of MS SQL Server ignores the filename path of the FILESTREAM filegroup.

The database was operational, ONLINE. I didn't want to leave it in this "state of files" too long cause I don't know the internals enough to tell what the garbage collector will do with the "inconsistent" files in filestream.

Conclusion of mine: Always check the final file layout after using CREATE DATABASE FOR ATTACH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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