triz-docs/docs/database-operations/sql-server-backup-restore/backing-up.md

sidebar_position	title
2	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).