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:
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;
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?