103 lines
3.4 KiB
Markdown
103 lines
3.4 KiB
Markdown
---
|
|
sidebar_position: 2
|
|
title: "Backing Up a Database"
|
|
---
|
|
|
|
# Backing Up a Database
|
|
|
|
---
|
|
|
|
Before performing a backup, choose the right type for your situation:
|
|
|
|
| Type | **What it captures** | **When to use** |
|
|
| ------------------- | ------------------------------------------ | ----------------------------------------------- |
|
|
| **Full** | The entire database | Weekly baseline; required before any other type |
|
|
| **Differential** | Changes since the last full backup | Daily; faster and lighter than a full backup |
|
|
| **Transaction Log** | All log activity since the last log backup | Hourly or more; enables point-in-time recovery |
|
|
|
|
---
|
|
|
|
## Full Backup
|
|
|
|
1. Open SSMS and connect to your SQL Server instance.
|
|
2. In the **Object Explorer** panel, expand the **Databases** node.
|
|
3. Right-click the database you want to back up, then select **Tasks → Back Up…**
|
|
|
|
|
|
|
|
4. The **Back Up Database** window will open. Under **Backup type**, make sure **Full** is selected.
|
|
|
|
|
|
|
|
5. Under **Destination**, you will see a default backup path. To change it:
|
|
1. Click **Remove** to clear the existing path.
|
|
2. Click **Add**, then type or browse to your desired folder and filename (e.g., `MyDatabase_Full_2025-04-22.bak`).
|
|
3. Click **OK**.
|
|
|
|
|
|
|
|
6. Click **OK** to start the backup.
|
|
|
|
|
|
|
|
7. A confirmation dialog will appear when it completes successfully.
|
|
|
|
|
|
|
|
---
|
|
|
|
## Differential Backup
|
|
|
|
Follow the same steps as a full backup, with one change at step 4: under **Backup type**, select **Differential** instead of Full.
|
|
|
|
|
|
|
|
**NOTE:** A differential backup requires that a full backup has already been taken for the same database.
|
|
|
|
---
|
|
|
|
## Transaction Log Backup
|
|
|
|
1. Right-click the database **→ Tasks → Back Up…**
|
|
2. Under **Backup type**, select **Transaction Log**.
|
|
3. Set the destination path and filename (e.g., `MyDatabase_Log_2025-04-22_10h00.trn`).
|
|
|
|
|
|
|
|
4. Click **OK** to run the backup.
|
|
|
|
|
|
|
|
**NOTE:** This option is only available when the database uses the **Full** or **Bulk-Logged** recovery model. To verify: right-click your database **→ Properties → Options →** check the **Recovery model** field.
|
|
|
|
|
|
|
|
---
|
|
|
|
## Scheduling Automatic Backups
|
|
|
|
You can automate recurring backups directly from the backup window without any scripting:
|
|
1. Configure your backup as described in the steps above, but **do not click OK yet**.
|
|
|
|
|
|
|
|
2. At the top of the window, click **Script → Script Action to Job**
|
|
|
|
|
|
|
|
3. The **New Job** window will open. Go to the **Schedules** page and click **New**.
|
|
|
|
|
|
|
|
4. Define your schedule (frequency, start time, recurrence) and click **OK**.
|
|
|
|
|
|
|
|
5. Click **OK** again to save the job. SQL Server Agent will run the backup automatically on the defined schedule.
|
|
|
|
|
|
|
|
**NOTE:** SQL Server Agent must be running for scheduled jobs to execute. You can check its status in Object Explorer (it appears as a node at the bottom of the tree).
|
|
|
|
|