Posted by: Colin Smith
I wanted to shed a bit more light on the bug that I talked about it my last post. So this is something that should really never happen. When you add a data file to SQL Server, it gets a file_id and that id is stored in sys.master_files. So if you add a bunch of data files to a database, you might have id’s 1 -primary, 2 – log, 3 – secondary, 4- secondary, 5 – secondary. This is the situation that my database was in at some point. Like shown below.
So you can see that I have file_id 1..5 making up my database. But now if I drop file ID 4. That will leave a gap.
This ID also has an entry in the backup file, so when you backup and restore the database, the file_id’s will match up. So the issue was that the virtualization software just assumes that the files will be sequential and no gaps will be in the order. Since this was not the case the VDB was not able to come online. In my last post, I talked about a hacky way to resolve that by modifying sys.master_files. (NOT RECOMMENDED).
The other way is to add as many data files back to the source database as you have gaps in your ID sequence. SQL Server will give these files the ID to fill in the gap. Show below.
Notice that the file_id 4 is back but the file name is actually file_id6. So once you add these files now you will not run into the bug.