-1

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

SCENARIO

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:

CREATE DATABASE DB2 ON 
(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'),
FILEGROUP WhatEver CONTAINS FILESTREAM DEFAULT 
(FILENAME = N'F:\FS\DB2_FSData', NAME = N'WhatEver_FS')
FOR ATTACH;

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?

Improve this question
4
  • 1
    Try specifying the NAME as well as the FILENAME for the files. learn.microsoft.com/en-us/sql/t-sql/statements/…
    – David Browne - Microsoft
    Oct 22, 2021 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, 2021 at 18:02
  • 2
    NAME is logical name of a file, which doesn't change when moving the database files.
    – David Browne - Microsoft
    Oct 22, 2021 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, 2021 at 18:44

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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