Today’s post is based on a conversation I had a while back with a colleague. They had received a database with pre-populated test data for use in their quality assurance environments. Because they had only received the data files, I had suggested them to use the ATTACH_REBUILD_LOG option of the CREATE DATABASE statement to rebuild the transaction log from scratch (Link). The conversation below took place the next day.

John: Thank-you for your help last night with the ATTACH_REBUILD_LOG option – we were able to successfully attach the database to our server and begin testing. However, I noticed something that appears to be an issue with SQL Server. When would you have a moment to discuss?

Me: You are welcome! While I do not see any obvious issues with the CREATE DATABASE statement in this scenario, it will be interesting to see if something is indeed broken. Let’s meet after lunch to discuss.

Just after lunch…

John: Thank-you for taking some time out to help clear my doubts. Let me first create a sample database, and check it’s recovery model.

--Step 01: Create a test database
USE master;
GO
CREATE DATABASE RecoveryModelTesting
ON PRIMARY (NAME = RecoveryModelTesting_Data,
            FILENAME='C:SQLDBsDataRecoveryModelTesting_Data.mdf')
LOG ON (NAME = RecoveryModelTesting_Log,
        FILENAME='C:SQLDBsLogsRecoveryModelTesting_Log.ldf');
GO
--Step 02: Check the recovery model of the database
USE RecoveryModelTesting;
GO
SELECT recovery_model, recovery_model_desc, name
FROM sys.databases
WHERE name = 'RecoveryModelTesting' OR name = 'model';
GO
/************************************************
Output
************************************************/
/*
recovery_model recovery_model_desc name
1 FULL model
1 FULL RecoveryModelTesting
*/

John: Notice that the recovery model for our test database is by default set to FULL, which is same as the model database. Now, allow me to detach the database and delete the transaction log file. We believe that this is the same process that was used to provide the database to us yesterday.

USE master;
GO
EXEC sp_detach_db @dbname = 'RecoveryModelTesting';
GO

John: Now that the database has been closed and detached from the SQL Server instance, let me delete the log file so that we can rebuild it when we CREATE the database using the ATTACH_REBUILD_LOG option.

(The screen-shot above is shows that the log file has been deleted by John.)

Me: So, the next step will now be to use the CREATE DATABASE statement with the ATTACH_REBUILD_LOG option just as you did yesterday, right?

John: That is correct. Let me attach the database by creating a new log file.

USE [master]
GO
CREATE DATABASE [RecoveryModelTesting]
ON  PRIMARY ( NAME = N'RecoveryModelTesting_Data',
              FILENAME = N'C:SQLDBsDataRecoveryModelTesting_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO
/************************************************
Output
************************************************/
/*
File activation failure. The physical file name "C:SQLDBsLogsRecoveryModelTesting_Log.ldf" may be incorrect.
New log file 'C:SQLDBsDataRecoveryModelTesting_log.ldf' was created.
*/

John: Now, let me check the recovery model of the database.

USE RecoveryModelTesting;
GO
SELECT recovery_model, recovery_model_desc, name
FROM sys.databases
WHERE name = 'RecoveryModelTesting';
GO
/************************************************
Output
************************************************/
/*
recovery_model   recovery_model_desc    name
1                FULL                   model
3                SIMPLE                 RecoveryModelTesting
*/

John: This is where I get confused. Why did the recovery model of the database reset to SIMPLE when the recovery model of the “model” database is FULL?

Me: It is indeed a very interesting observation, John. Before I answer your question, I would like you to answer mine – What is the primary purpose of the transaction log?

John: To recover a database to a point in time in case something goes wrong.

Me: Correct. The log can only recover from the point in time it was created, and not to anytime before. In this case, the transaction log file was recreated when we used the CREATE DATABASE statement with the ATTACH_REBUILD_LOG option because the database did not come with a log file. Because the database log file has just been created, the recovery model is set to the simplest of all – the SIMPLE recovery model. The user can then manually choose the correct recovery model according to the requirement.

John: But, can’t SQL Server attempt to come close to the original recovery model?

Me: First of all, generally data files would be coming from a 3rd party (a vendor, or a completely different server). Therefore, there is no way of the system knowing the previous recovery model of the database.

Secondly, setting the recovery model to whatever is dictated by the model database might be too excessive. Let’s assume that the model database follows the FULL recovery model. In case the database is being attached is for reference or for read-only purposes, setting to FULL may not be required. Hence, it is always better to start off from the simplest – the SIMPLE recovery model and then allow the user to configure the recovery model to suit the purpose.

John: Now it makes complete sense. Thank-you very much for your time!

Me: I am glad I could help. Have a good afternoon!

Until we meet next time,

Be courteous. Drive responsibly.