What is the Recovery Model for Newly Created Database?

SQL server offers a facility to all database administrator for managing transaction log that is known as Recovery model. It will decide that you are going to manage your transaction log or not. There are three types of recovery model in SQL server: Simple, Full, & Bulk-Logged. If you have chosen simple recovery model for your database then you will be unable to make transaction log backup but you can make backup of transaction log in full & bulk-logged recovery model.

Recovery Model for Newly Created Database: Default recovery model of SQL server database is same like as recovery model of Model database. Model Database recovery model depends upon the edition of SQL server. If your model database is in 'Full' recovery model then your newly created database will also in 'Full' recovery model. To verify this lets create a database.

Simple create statement for creating a database:

CREATE DATABASE [New Test Database]

GO

# 'New Test Database' is the name of database.

Now run a query to select recovery model for 'model' & 'new test database'.

SELECT Name AS 'Databasename', recovery_model_desc

FROM sys.databases

WHERE name = 'Model' OR Name = 'New Test Database'

GO

Output:

	Databasename	recovery_model_desc
1	Model	Full
2	New Test Database	Full

As you can see that both the databases have same recovery model i.e 'Full'.

If you would like to change the recovery model for your database then you can change it with the help of SQL Server Management Studio (SSMS) or Transact SQL (T-SQL).

Is Your SQL Server Database Really in Full Recovery Model? It has been noticed that your SQL server database is not in full recovery model as conformed by 'Select recovery model' query. I am not telling anything wrong you guys! You can verify this at you end also as I have done.

As discussed in the above paragraph of article, you can make backup of transaction log if your database is in full recovery model & also confirmed by query that my newly created database (NEW Test Database) is in full recovery mode that means I am able to take transaction log backup right! But when I try to take transaction log backup got below error message:

BACKUP LOG New Test Database TO DISK = 'C:\DATA\Backup\New_Test_Database.BAK'

Got error message on my screen:

Msg 4214, Level 16, State 1, Line 1

Backup log cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

Backup log is terminating abnormally.