As a SQL Server user, you may come across various issues while working with the database. One such issue was brought to my attention during a recent conference. A young DBA approached me confidently, claiming to have found a bug in SQL Server Restore. Intrigued, I asked him to provide more details before jumping to conclusions.

The DBA explained that in order to restore a database from a backup, you need to have the same database already created. This statement seemed incorrect to me, so I asked him to email me the repro steps to investigate further. After eagerly waiting for his email, I finally received it.

The DBA’s repro steps were as follows:

CREATE DATABASE SQLAuth
GO
BACKUP DATABASE SQLAuth TO DISK = 'C:\Temp\SQLAuth.bak' WITH FORMAT
GO
sp_detach_db 'SQLAuth'
GO
RESTORE DATABASE SQLAuth FROM DISK = 'C:\Temp\SQLAuth.bak'
GO

Upon running the last command for restore, the DBA encountered the following error:

Msg 3142, Level 16, State 1, Line 7
File "SQLAuth" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth.mdf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3142, Level 16, State 1, Line 7
File "SQLAuth_log" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3119, Level 16, State 1, Line 7
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

The error message clearly indicates the cause of the restore failure. Since the database was detached, the MDF and LDF files were still present at the original location. SQL Server does not automatically overwrite these files unless explicitly specified. To overwrite the files, the DBA can use the “WITH REPLACE” clause in the RESTORE command.

If the database is not detached and the restore is performed on top of an existing database, a slightly different message is displayed:

Msg 3159, Level 16, State 1, Line 5
The tail of the log for the database "SQLAuth" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

This behavior is a safety mechanism in SQL Server. It prompts the user to confirm their actions, similar to when you try to paste a file with the same name in Windows. SQL Server ensures that you are aware of the potential consequences before proceeding.

I was relieved to discover that this was not a bug within SQL Server. It is always important to validate and experiment with different scenarios when working with backups in SQL Server. I am grateful to the DBA for bringing this issue to my attention, as it allowed me to further explore and understand the intricacies of SQL Server restore functionality.