How to Back Up and Restore a MSSQL Database (.bak File)

Quick answer: Back up a MSSQL database to a .bak with BACKUP DATABASE ... TO DISK, copy the file to the target, and restore with RESTORE DATABASE ... FROM DISK. If folder paths differ, add MOVE clauses; run RESTORE FILELISTONLY first to see the logical file names.

A native MSSQL backup produces a single .bak file that captures the entire database - schema, data, and indexes. It is the simplest way to move a database between servers or keep a restore point. Here is the full round trip.

Back up a database to a .bak file

In SSMS, right-click the database, choose Tasks > Back Up, and set:

Backup type:  Full
Destination:  Disk
Path:         C:\Backups\YourDatabase.bak

Click OK. For a scriptable version, use T-SQL:

BACKUP DATABASE [YourDatabase]
TO DISK = N'C:\Backups\YourDatabase.bak'
WITH FORMAT, INIT, COMPRESSION;

COMPRESSION shrinks the file significantly on supported editions.

Restore a .bak file

Copy the file to the target server, then in SSMS right-click Databases > Restore Database, choose Device, and browse to the .bak. Or in T-SQL:

RESTORE DATABASE [YourDatabase]
FROM DISK = N'C:\Backups\YourDatabase.bak'
WITH REPLACE;

If the target server uses different folder paths, add MOVE clauses to point the data and log files at valid locations:

WITH MOVE 'YourDatabase'     TO 'D:\Data\YourDatabase.mdf',
     MOVE 'YourDatabase_log' TO 'D:\Data\YourDatabase_log.ldf',
     REPLACE;

Run RESTORE FILELISTONLY FROM DISK = N'...' first to see the logical file names to use in the MOVE clauses.

Common restore errors

"The backup set holds a backup of a database other than the existing" means you are restoring over the wrong database - use WITH REPLACE or restore to a new name. A version error means the backup came from a newer SQL Server; you cannot restore a newer backup onto an older engine.

Frequently asked questions

Can I restore a newer backup onto an older SQL Server?
No - you cannot restore a backup from a newer version onto an older engine.

What does backup set holds a backup of a different database mean?
You are restoring over the wrong database; use WITH REPLACE or restore to a new name.

Managed SQL Server on a SoftSys managed Windows VPS includes scheduled backups handled by our team, so you always have recent restore points without maintaining the job yourself.