Long before I turned to Data Warehousing and OLTP replica environments such as Operational Data Stores as a form of data redundancy strategies, I had been using database snapshots. During that time, the analytics and reporting teams were directed towards a database snapshot as a data source for their development. Another benefit of having a database snapshot, is the ease of knowing that should unintended changes be committed in source, you could easily revert the operation by restoring source database off the snapshot.
However, working with database snapshots is not always a happy affair. I was recently reminded of some of the frustrations that may come up when one is attempting to create database snapshots. One such frustration is trying to understand the meaning of the following error message:
Msg 5127, Level 16, State 1, Line 1
All files must be specified for database snapshot creation. Missing the file “SingleFileDB”.
Having setup a test environment to understand the causes that could lead to 5127 error code, I realised that under different scenario, the meaning behind the error code could be interpreted differently – which could be burdensome when you are trying to identify the root cause of the error. In this article, we try to alleviate the frustrations by exploring all causes that could lead to different interpretations of the 5127 error code. We conclude by proposing a stored procedure that could be utilised to create database snapshots without encountering much of the 5127 error code.
CASE STUDY
Likewise, a proper discussion of this topic is inadequate without illustrating our discussion points by means of examples and screenshots. Therefore, for this discussion, I setup an instance of SQL Server 2012 with self-explanatory databases SingleFileDB (which has a single data file) and MultiFilesDB (which is made up of multiple data files).
Single Data File Scenario
Figure 1 lists all the files that belong to SingleFileDB database:
Because we only have a single data file linked to SingleFileDB database, its script for creating a database snapshot becomes very simple, as shown in Figure 2:
However, if we deliberately modify the script in Figure 2 and enter an incorrect data file name (i.e. SingleFileDB1 instead of SingleFileDB) as shown in Figure 3, we receive the 5127 error code.
Thus, in a scenario whereby the source database has a single data file, the 5127 error code can only be interpreted as referring to a mismatch between the provided data file name in the Name parameter (of the script) and that of the source database’s data file name. In order to resolve this, you should refer to the “Missing the file” part of the error message whereby as illustrated in Figure 3, you are given the expected name that should have been provided (in our example, it should have been).
Multiple Data File Scenario
Unlike a single data file scenario, there are several causes that could lead to 5127 error code whilst attempting to create a snapshot against a source database with multiple data files. One of the causes is similar to the single data file scenario in that it could mean that the provided value in the Name parameter does not match that of the expected data file name, as shown in Figure 4 whereby the provided name of MultiFilesDB1 does not match with the expected value of MultipleFilesDB.
Another cause of the 5127 error code in a multi-data file scenario is that you may have skipped a data file in your list of data files that make up a given source databases. For instance, in Figure 5 I have intentionally provided an incorrect file name (MDF2TEST instead of MDF2), however, the error message returned only relates to a missing MultiFilesDB file.
This means that when the script was being validated, the query processor realised that I haven’t included a file that should have been part of my create database snapshot script. The reason it picked up on the missing file was by going through the sys.database_files system view. Figure 6 shows the results of the sys.database_files system view in which MultipleFilesDB database is made up of 3 data files and in terms the file_id order, MultipleFilesDB data file was created ahead of the MDF2 file and thus should have been added in my create database snapshot script.
Dynamically Create SQL Server Database Snapshots
One limitation of the 5127 error code is that it doesn’t return a consolidated list of all missing files that should have been included in your create database snapshot script. Thus, I setup a stored procedure (SP) that could be used to address this limitation by dynamically creating database snapshot which guarantees correct capture of data file names and proper listing of all associated files of a source database. The stored procedure can be downloaded here. The only requirement for the SP is that you parse source database name parameter.
Although you can go through the SP in detail once you have downloaded it, I felt that I need to bring a few things about the SP into your attention:
As a form of naming convention, all database snapshots created via my SP, will be suffixed with _snap
The dynamically capabilities of the SP are made possible by a T-SQL cursor. Although, the usage of the cursor shouldn’t be resource intensive, nevertheless beware that the SP uses a T-SQL cursor.
Finally, because I understand the consequences of incorrectly dropping database objects – in a case of an existing database snapshot, the SP will not drop and recreate such an object instead it skip the creation of the database snapshot and returns a “database snapshot exists” message shown in Figure 7:
Figure 7: Database snapshot exists message
CONCLUSION
In this article we have attempted to remove the ambiguities of SQL Server 5127 error code by recreating the error within a single data file database scenario as well as in a multiple data files database scenario. It was during the scenarios that we realised that the message is two-fold: it may mean indicate a mismatch between a specified file name and a given source database; it could also mean that you have not listed all the files of source database with a multiple data files. The underlying error with this message is that we need to find a mechanism to dynamically retrieve data files. Thus, a stored procedure – sp_DynamicDBSnapshotCreator – was developed for the sole purpose of eliminating data filenames mismatch and successfully create a database snapshot.
Download sp_DynamicDBSnapshotCreator scripts here
References
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu