Skip to main content

Backing Up a Database

Before performing a backup, choose the right type for your situation:

TypeWhat it capturesWhen to use
FullThe entire databaseWeekly baseline; required before any other type
DifferentialChanges since the last full backupDaily; faster and lighter than a full backup
Transaction LogAll log activity since the last log backupHourly 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…

SSMS Backup Menu

  1. The Back Up Database window will open. Under Backup type, make sure Full is selected.

Backup Type Full

  1. 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.

Backup Destination

  1. Click OK to start the backup.

Start Backup

  1. A confirmation dialog will appear when it completes successfully.

Backup Success

Differential Backup​

Follow the same steps as a full backup, with one change at step 4: under Backup type, select Differential instead of Full.

Backup Type Differential

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).

Backup Type Transaction Log

  1. Click OK to run the backup.

Start Log 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.

Check Recovery Model

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.

Configure Backup

  1. At the top of the window, click Script → Script Action to Job

Script to Job

  1. The New Job window will open. Go to the Schedules page and click New.

New Schedule

  1. Define your schedule (frequency, start time, recurrence) and click OK.

Define Schedule

  1. Click OK again to save the job. SQL Server Agent will run the backup automatically on the defined schedule.

Save Job

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).

SQL Server Agent Status