What is the default recovery model in SQL Server?

The default recovery model is Full for new databases on newer SQL Server versions, but it can vary depending on installation and configuration.

Can I change the recovery model after creating a database?

Yes, you can change the recovery model anytime using the ALTER DATABASE command.

Why should data and log files be on separate disks?

Separating data and log files reduces disk contention and improves overall database performance.

RPATechnology.in

SQL Server Creating Databases - Database Properties Tutorial | RPATechnology.in

Topic 4112 min readStructured Tutorial

Creating Databases in SQL Server: Understanding Database Properties

Quick Answer

In SQL Server, database properties define how a database behaves, including file locations, size, growth settings, recovery model, and collation. Setting these properties correctly during database creation ensures optimal performance, data integrity, and maintenance.

Learning Objectives

Identify key database properties in SQL Server during database creation.
Explain the impact of recovery models on database backups and restores.
Configure file settings such as size, growth, and file paths for SQL Server databases.

Introduction

Creating a database in SQL Server involves more than just naming it. Understanding the properties that define how the database stores data and handles transactions is essential.

This tutorial covers the main database properties you need to know when creating a database, including file configuration, recovery models, and collation settings.

Course Progress0%

Was this topic helpful?

Your feedback helps us improve this lesson.

Topic information

Author

RPATechnology Team

Last Updated

June 2026

Suggested learning path

Related Courses

Continue learning with related technologies and tracks.

Programming LanguagesBeginner FriendlyCareer Focused

C# Programming Course for Beginners to Advanced | Learn C# and .NET

Master C# programming with a complete beginner-to-advanced course covering OOP, collections, LINQ, async programming, Entity Framework Core, ASP.NET Core, Web APIs, design patterns, and real-world projects.

Structured lessons

View Course

Key Database Properties in SQL Server

When creating a database, SQL Server allows you to configure several properties that affect its behavior and performance.

These properties include file settings, recovery model, and collation, each serving a specific purpose.

Database files: primary data file, secondary data files, and log files.
File size and autogrowth settings.
Recovery model: Simple, Full, or Bulk-logged.
Collation: defines character set and sorting rules.

Database Files and Filegroups

A SQL Server database consists of at least two files: a primary data file (.mdf) and a transaction log file (.ldf). Additional secondary data files (.ndf) can be added.

Files are grouped into filegroups, which help organize data storage and improve performance.

Primary data file contains startup information and primary data.
Transaction log file records all transactions for recovery.
Secondary files can be placed on different disks for performance.

File Size and Autogrowth Settings

You can specify initial file sizes and configure autogrowth to allow files to expand automatically as data grows.

Proper autogrowth settings prevent frequent small growths that can degrade performance.

Set initial size based on expected data volume.
Configure autogrowth in MB or percentage.
Avoid very small autogrowth increments.

Recovery Models

The recovery model determines how transactions are logged and how the database can be restored after failure.

SQL Server offers three recovery models: Simple, Full, and Bulk-logged.

Simple: minimal logging, no point-in-time recovery.
Full: full logging, supports point-in-time recovery.
Bulk-logged: minimal logging for bulk operations, with some recovery limitations.

Comparison of SQL Server Recovery Models
Recovery Model	Logging Type	Backup Type Supported	Use Case
Simple	Minimal	Full database backups only	Development or test environments
Full	Complete	Full, differential, and transaction log backups	Production environments requiring point-in-time recovery
Bulk-logged	Minimal for bulk operations	Full and bulk-logged backups

Collation Settings

Collation defines the rules for character data storage, comparison, and sorting.

Choosing the correct collation ensures proper handling of case sensitivity, accent sensitivity, and language-specific sorting.

Set collation at database creation or use server default.
Important for multilingual databases.
Affects string comparisons and ORDER BY clauses.

Examples

Creating a Database with Custom Properties

CREATE DATABASE SalesDB
ON PRIMARY (
    NAME = SalesDB_Data,
    FILENAME = 'C:\SQLData\SalesDB_Data.mdf',
    SIZE = 50MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = SalesDB_Log,
    FILENAME = 'C:\SQLLogs\SalesDB_Log.ldf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);

ALTER DATABASE SalesDB SET RECOVERY FULL;
ALTER DATABASE SalesDB COLLATE Latin1_General_CI_AS;

This script creates a database with specified data and log file locations, initial sizes, autogrowth increments, sets the recovery model to Full, and applies a case-insensitive, accent-sensitive collation.

Learning Objectives

Introduction

Was this topic helpful?

Topic information

Recommended Next Topics

ALTER DATABASE

DROP DATABASE

Database Management

Best Practices

Related Topics

Related Courses

C# Programming Course for Beginners to Advanced | Learn C# and .NET

Key Database Properties in SQL Server

Database Files and Filegroups

File Size and Autogrowth Settings

Recovery Models

Collation Settings

Creating a Database with Custom Properties

Example: Creating a Database with T-SQL

Practical Example

Examples

Best Practices

Common Mistakes

Hands-on Exercise

Create a Database with Custom Properties

Interview Questions

What is the purpose of the recovery model in SQL Server?

Why is it important to configure autogrowth settings properly?

What does collation affect in a SQL Server database?

MCQ Quiz

1. What is the primary purpose of the transaction log file (.ldf) in a SQL Server database?

2. Which recovery model in SQL Server allows point-in-time recovery and supports full transaction logging?

3. When configuring autogrowth for a database file, which practice helps maintain optimal performance?

4. What role does collation play when creating a SQL Server database?

5. Which of the following statements about filegroups in SQL Server is TRUE?

Key Takeaways

Frequently Asked Questions

What is the default recovery model in SQL Server?

Can I change the recovery model after creating a database?

Why should data and log files be on separate disks?

Summary

CREATE TABLE

Column Definitions

Java Programming Course for Developers and Interview Success | RPATechnology.in

Complete JavaScript Tutorial for Beginners to Advanced | JavaScript Programming Course

Complete MongoDB Tutorial for Beginners to Advanced | MongoDB Course

Complete MySQL Tutorial for Beginners to Advanced | MySQL Database Course

Complete Python Tutorial for Beginners to Advanced | Python Programming Course