In famous sci-fi Iron Man series Justin Hammer said "I love peace, but we live in a world of grave threats. Threats that Mr. Stark will not always be able to foresee." The statement is so true for the DBA's world where there are potential threats to data security. There is a constant fear of losing data because of the mistakes of some developer or support person.

This is a scenario I encountered recently and I thought of documenting it. This is a rare situation but we must be ready to face it if it occurs. I had to move the user database files for my TestDB to a new partition on the G:\ drive and I decided to also move tempdb to the G:\ drive at the same time. I used the code below to move the following files.

For TestDB:

Alter Database Testdb Modify File (Name=TestDB, FileName ='G:\Data\TESTDB.MDF')
Alter Database Testdb Modify File (Name=TestDB_log, FileName ='G:\Data\TestDB_log.ldf')

The output of the this code is shown in the screen shot below:

For tempdb I executed:

Alter Database tempdb Modify File (Name=tempdev, FileName ='G:\Data\tempdb.mdf')
Alter Database tempdb Modify File (Name=templog, FileName ='G:\Data\templog.ldf')

The output of the that code is shown in the screen shot below:

The SQL Server instance was stopped and the files for the user database (TestDB ) were physically moved to the new directory (G:\Data). The tempdb database files are created every time the SQL Server is restarted so there was no need to move those database files. The SQL Server instance was started and then a query was executed on the TestDB as written below.

select name,address into #table from dbo.contact
select * from #table 

The output of the code above is shown in the screen shot below:

I had an issue one fine day when I realized that I had run out of space on my workstation's C:\ drive. I went to the Disk Management utility and deleted the G: partition to free up space. I then extended the C: partition. I then tried to open SSMS and found that the SQL Server was not started. I opened up SQL Server Configuration Manager to see the state of the SQL Server. The server was in auto start mode, but it was not running. I tried to start the server, and it did not show any error, but the server did not start. I went to the Windows error log and found the error shown below.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'G:\Data\tempdb.mdf'.

The issue was tempdb database files had been moved to G:\Data\ and that did not exist anymore. When SQL Server tried to restart and create tempdb, it failed. Now I had to bring the SQL Server up somehow and change the file path of tempdb to its default path. To do this, I had to start the SQL Server in single user mode and then make the changes. I stopped the SQL Server Agent from the configuration manager. I opened a command prompt and ran the code below to switch on the SQL Server with minimal configuration.

cd C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
sqlservr.exe -f

This started the SQL Server in the minimal configuration single user mode as is shown in the screen shot below.

This started SQL Server in single user mode. I opened a new query window in SSMS and connected to the master database and then altered tempdb to point to a physically existing disk folder as shown below.

alter database tempdb move file (filename=tempdev, FileName ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf')
alter database tempdb move file (filename=templog, FileName ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf')

I got the message that the tempdb has been re-configured and that the change will take place when SQL Server is restarted as shown in the screen shot.

I closed the query window and the command line that was still running. I started the SQL Server instance again and this time it worked. After the server came up I restored the user database from a backup file using SSMS and executed the same query on the database to find the same result as shown below.

Conclusion

We should be well aquainted with the command line tools of SQL Server and we should prepared for the disasters that will occur. In this case, knowing how to start SQL Server in single user mode and undo previous changes can ensure that your instance continues to run.