How to Back Up and Restore SQL Server Databases with SSMS

A SQL Server backup is useful only if it can be restored. SQL Server Management Studio (SSMS) provides a straightforward interface for creating and restoring a full .bak file, but production systems also need scheduled backups, off-server copies, retention rules, and regular restore tests.

Before you begin: Confirm the database name, free disk space, recovery objectives, and the SQL Server service account's access to the backup location. Backing up or restoring the wrong production database can cause downtime or data loss.

Create a full backup in SSMS

1. Open SSMS and connect to the correct SQL Server instance.
2. Expand Databases in Object Explorer.
3. Right-click the database and select Tasks > Back Up.

4. Set Backup type to Full. For an ad-hoc backup that should not change the differential backup base, select Copy-only backup.
5. Under Destination, remove an unwanted default entry and select Add.
6. Enter a full server-side path ending in .bak, such as D:SQLBackupsWideWorldImporters_full.bak.
7. On Media Options and Backup Options, review overwrite, checksum, compression, and encryption settings. Do not overwrite an existing backup unless the retention policy explicitly calls for it.
8. Select OK and wait for the success message.

The path is resolved by the SQL Server service, not by the computer running SSMS. For a network share, use a UNC path and grant the SQL Server service account the required share and file permissions.

Create the same backup with T-SQL

BACKUP DATABASE [WideWorldImporters]
TO DISK = N'D:SQLBackupsWideWorldImporters_full_20260826.bak'
WITH COPY_ONLY,
     CHECKSUM,
     STATS = 10;
GO

Remove COPY_ONLY when this command is part of the normal backup chain. Use a unique filename for each run, or manage media sets deliberately; otherwise an old file can contain multiple backup sets and make restore selection confusing.

Verify and test the backup

RESTORE VERIFYONLY checks that the backup set is complete and readable, but it does not validate the logical structure of every page. A test restore followed by DBCC CHECKDB is a stronger validation.

RESTORE VERIFYONLY
FROM DISK = N'D:SQLBackupsWideWorldImporters_full_20260826.bak'
WITH CHECKSUM;
GO

Keep at least one protected copy outside the SQL Server host. A backup stored only on the same disk or server can be lost with the database during hardware failure, ransomware, or administrator error.

Restore a full backup with SSMS

1. Connect to the target SQL Server instance.
2. Right-click Databases and choose Restore Database.

3. Under Source, choose Device and select the ellipsis button.
4. Select Add, locate the .bak file as seen by the server, and confirm it.
5. Review the selected backup set, destination database name, and restore timeline.
6. Open the Files page. When restoring under a new name or to another server, choose valid destination paths for every data and log file.
7. Open the Options page. Use Overwrite the existing database (WITH REPLACE) only when you have verified the target and intend to replace it.
8. Disconnect applications if restoring over an existing database, then select OK.

Restore to a new database name with T-SQL

First retrieve the logical filenames stored in the backup:

RESTORE FILELISTONLY
FROM DISK = N'D:SQLBackupsWideWorldImporters_full_20260826.bak';
GO

Then use those exact logical names in the MOVE clauses:

RESTORE DATABASE [WideWorldImporters_Test]
FROM DISK = N'D:SQLBackupsWideWorldImporters_full_20260826.bak'
WITH MOVE N'WideWorldImporters' 
       TO N'D:SQLDataWideWorldImporters_Test.mdf',
     MOVE N'WideWorldImporters_log'
       TO N'D:SQLLogsWideWorldImporters_Test_log.ldf',
     RECOVERY,
     CHECKSUM,
     STATS = 10;
GO

The logical filenames vary by database; do not assume they match the example. Restore to a nonproduction name first when validating an unfamiliar backup.

Important restore considerations

• A backup created by a newer SQL Server version cannot be restored to an older SQL Server version.
• A TDE-encrypted database requires the certificate and private key on the target instance.
• Restoring a database does not automatically recreate server-level logins, SQL Agent jobs, linked servers, or other instance objects.
• For point-in-time recovery under the Full or Bulk-logged recovery model, plan transaction-log backups and restore the sequence with NORECOVERY until the final step.
• Document who can run backups and restores; both operations expose sensitive data and require elevated permissions.

A full backup is not a backup strategy

Define recovery point and recovery time objectives, schedule full/differential/log backups accordingly, monitor job failures, protect backup credentials, and perform test restores on a regular cadence. Record the restore time and every dependency needed to make the application usable.

Microsoft's current SSMS backup and restore quickstart provides version-specific interface details. Use it alongside your organization's retention, encryption, and disaster-recovery requirements.