Posted by: Colin Smith
So in my testing of the Database Virtulization product I ran into a most interesting bug that really caused me some pain to figure out. When I provisioned the VDB, the database showed up on the target instance but was in a restoring state. Checking the error log I found that the database was unable to start do to a file_id in the file not matching what SQL Server Expected due to what was in sys.master_files. The ID of one of the data files was incorrect in sys.master_files. To resolve this I had to update the sys.master_files catalog directly. I do not recommend doing this and I did it only because I knew that if something catastrophic happened… Well I just did not care about this database at all. First I went and found Paul Randal talking about how to update system catalogs. Please read and you will notice that Paul is also cautioning you against doing this.
Here are the steps that I took.
- Bring the SQL Server Instance offline.
- Restart the SQL Server Instance in Single user mode.
- Sqlsevr –m
- Connect to the instance using the DAC
- Sqlcmd /A
- Allow ad hoc updates to system catalogs.
- Sp_configure ‘allow updates’, 1
- Reconfigure with override
- Update the catalog with the correct file id
- Update sys.master_files
- Set file_id = 6
- Where database_id = 8
- And file_id = 5
- Shutdown the SQL Server Instance
- Restart the SQL Server Instance in regular mode.
And just like that my VDB was back.
More on the virtulization product to come.
And we notified the vendor and they are working on a fix as what I did just is not acceptable for a fully baked product.