Recently a friend had an issue with their SQL Server as a result of a file change in their SQL Server database. In this short piece, I want to highlight the dangers of making some changes to an online database that might cause an unexpected outage. This is really the same whether you are changing the filename or the file extension. Since extensions are more common, I'll use those in my example, but this works with names as well.

We have an article on renaming files that gives the basic procedure, but doesn't explain the danger of not taking an outage.

Note: this is based on actual events, but a few details have been changed to protect privacy.

Noticing Something Amiss

An administrator noticed that all the SQL Server database files on a drive were named with the .mdf extension. That might not be unusual, but in this case there were dozens of files, but only about 5 databases. This was a system that wasn't heavily used, and it had existed for many years, across a number of administrators. As a result the management of files wasn't carefully watched.

I'll simulate this with the following code. If you need to test this on your system, just change the paths to match your drives.

CREATE DATABASE [OneWorld]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'OneWorld',          FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Demo\OneWorld.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
( NAME = N'OneWorld_Archive',  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\OneWorld_Archive.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
( NAME = N'OneWorld_Archive2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\OneWorld_Archive3.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
( NAME = N'OneWorld_Archive3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\OneWorld_Archive4.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'OneWorld_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Demo\OneWorld_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
CREATE DATABASE [Accounting]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Accounting',          FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Demo\Accounting.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
( NAME = N'Accounting2',  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\Accounting2.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP Secondary
( NAME = N'Accounting3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\Accounting3.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
( NAME = N'Accounting4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\Accounting4.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Accounting_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Demo\Accounting_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
CREATE DATABASE Vacation
GO
CREATE DATABASE Suppliers
GO
CREATE DATABASE HR
GO

If we look at SSMS, we see the five databases.

If we look in the filesystem, we see this the databases with multiple files have them all set as .mdf, Primary Data Files in the folder.

There's nothing wrong with this, but these aren't named according to a convention and someone might think these are all separate databases, or think they're copies, or something else. As a result, an administrator later on might want to rename files.

A Quick Fix

The ALTER DATABASE command says that this will let you rename the files. I've submitted a PR (that might be live), but someone might not carefully read this and assume that the MODIFY FILE argument will actually change the physical files. Let's look at how we might do this.

To fix the OneWorld database, we would use ALTER DATABASE in this way. Let's rename the OneWorld_Archive file with this code:

ALTER DATABASE OneWorld
 MODIFY FILE (NAME= 'OneWorld_Archive', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\OneWorld_Archive.ndf')
 GO

The result is we get this message back:

The file "OneWorld_Archive" has been modified in the system catalog. The new path will be used the next time the database is started.

That's a big ambiguous to me. It doesn't say I need to also rename the file in the filesystem. It also doesn't imply I don't need to bother. Instead, I'm confused. If I look at the filesystem, I see the same view:

Restart the instance without manually renaming the file

I've renamed the file above with ALTER DATABASE. Let's actually do that with the Accounting database as well with this code, assuming that when we're fixing one thing, we'll go ahead and fix another:

ALTER DATABASE Accounting
 MODIFY FILE (NAME= 'Accounting3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\Accounting3.ndf')
ALTER DATABASE Accounting
 MODIFY FILE (NAME= 'Accounting4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\Accounting3.ndf')
GO

Now, let's restart the instance. When this completes, we see this:

The Accounting database is in Recovery Pending state. If we check the error log, we see that the database could not find the files because they were renamed. We will see OS error 2 in the error log, as shown here.

There are similar messages for the OneWorld database. If you randomly restart an instance, or worse, maybe your Windows group restarts the instance because of a patch, you might be surprised to see this.

Recovery

How do you recover from this? Imagine you just get a call and you find the database is in recovery and users can't connect? I hope that you scan the error log first thing and notice the restart. In fact, when you are faced with issues, the error log is a good place to start, noting the time of entries in relation to the ticket and current time.

Once you see the issues, the first thing to do is go check the files. If you do that, and you compare the names, I hope you notice the extension is different. This is a good reason to ensure your Explorer shows the file extensions and not just the name. The filename is correct here, but not the extension. Let me rename the extensions for these three files.

Note that when I do this, I ought to receive a warning from the UAC system, as shown below.

Once I've done that, I can take the database offline and then back online to use the file again. This will allow me to quickly get the system working. SSMS sometimes has issues with the GUI interaction when a database is in recovery, so I would recommend this code:

ALTER DATABASE OneWorld SET OFFLINE
ALTER DATABASE OneWorld SET ONLINE

This completes, and I can refresh the database list to see my database online.

I can repeat this for Accounting and I'll have my system back.

Conclusion

Changing the filename or extension is an easy task. We've written about that before at SQLServerCentral, but the important thing to remember and know is that this will require an outage for your database. This is because the database must be offline when the files are actually renamed. This requires setting the database to OFFLINE or stopping the instance.

The cautionary tale from this article is that if you don't do that when you rename the files with ALTER DATABASE ... MODIFY FILE, you might get surprised when the instance restarts at a later date. Or worse, another administrator gets surprised while performing some other action. In a crisis situation, you or someone else might not realize that the database in Recovery mode is due to a file rename and not some other issue. I have seen sysadmins or DBAs panic a bit and perform multiple restarts, or worse, start a restore right away because they are concerned.

If you decide to rename the filenames or extensions for some reason, please only do so when you can take the database offline and back on. This can be a quick outage, but it is an outage.

I also recommend that you use the standard extensions (mdf, ldf, ndf) and let SQL Server choose default names based on your logical names. This will keep the settings aligned and increases the likelihood that future DBAs will understand how your database is configured.