SQL Server中.bak文件与.script文件有何区别？

Hey there! Let's break down the key differences between .bak and .script files in SQL Server—they serve totally distinct purposes, so it’s easy to mix them up if you’re new to managing SQL databases.

This is the biggest dividing line between the two:

• .bak (Backup File) : This is SQL Server’s native binary backup format. Think of it as a full, exact snapshot of your database (or a portion of it) at a specific point in time. Its sole job is to let you restore your database to that exact state, down to the last transaction.
• .script (SQL Script File) : This is a plain-text file packed with SQL commands (like CREATE TABLE, INSERT, CREATE PROCEDURE). It’s a step-by-step instruction manual to rebuild your database’s structure, and optionally its data, from scratch. It doesn’t capture the database’s binary state—just the logic to recreate it.

• .bak: Binary format, completely unreadable by humans. Open it in Notepad, and you’ll see garbled characters. It stores raw database pages, transaction logs, metadata, and all other low-level storage details that SQL Server needs to reconstruct the database.
• .script: Plain ASCII/UTF-8 text, fully human-readable and editable. You can open it in any text editor or SQL Server Management Studio (SSMS) to tweak commands, remove objects, or modify data before execution. For example, a script might start with table creation statements, followed by INSERT commands to populate those tables.

When to use .bak files: #

• Disaster recovery: Regular full/incremental backups to protect against data loss.
• Full database replication: Quickly copy an entire database (including all data, indexes, permissions, and configuration) to another server.
• Point-in-time recovery: If you need to restore the database to a specific moment (thanks to transaction log backups paired with full .bak files).

When to use .script files: #

• Structure-only migration: Moving just the schema (empty tables, views, stored procedures) to a development or testing environment.
• Version control: Since it’s plain text, you can commit scripts to Git or other version control systems to track changes to your database structure over time.
• Sharing specific objects: Sending a single stored procedure or view to a colleague—you don’t need to share the entire database backup.
• Custom data migration: When you need to edit data before importing (e.g., cleaning up test data, modifying values for a new environment).

Restoring a .bak file: #

You can’t "run" a .bak file directly—you need to use SQL Server’s restore tools. This can be done via SSMS’s graphical "Restore Database" wizard, or with T-SQL:

RESTORE DATABASE MyProductionDB
FROM DISK = 'C:\SQLBackups\MyProductionDB_Full.bak'
WITH REPLACE, RECOVERY;

The restore process rebuilds the database’s underlying files (MDF/LDF) and restores all data and objects to their state at backup time.

Executing a .script file: #

Simply run the script in SSMS, sqlcmd, or any SQL client. The database engine executes each SQL command in sequence. For example, a script might look like this:

-- Create Users table
CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL
);

-- Insert sample data
INSERT INTO Users (Username, Email)
VALUES ('john_doe', 'john@example.com'), ('jane_smith', 'jane@example.com');

You can edit this script before running—say, change the email domain or add more columns—something you can’t do with a .bak file.

• .bak files: Typically much smaller than equivalent script files, thanks to built-in compression (default in modern SQL Server versions). Restoring a .bak is also faster, as it’s a direct write to database files rather than parsing and executing hundreds/thousands of SQL commands.
• .script files: Can get very large if they include lots of data (each INSERT statement is plain text). Executing a data-heavy script is slower, especially for large datasets, since each row has to be processed via SQL commands.

内容的提问来源于stack exchange，提问作者Kiến Nguyễn