How to Restore a SQL Server Backup (.bak File), Step by Step

November 2026

Before you start: never restore over live data first

Many billing, ERP and accounting programs in Indian offices keep their data in SQL Server, often the free Express edition. When something goes wrong, the first idea is usually "restore yesterday's backup over it". That is the step that most often makes things worse:

  • If the backup is older than you think, today's bills are gone.
  • If the backup is damaged, you now have no working database at all.
  • If the wrong file was picked, you have overwritten good data with bad.

So this guide restores the backup under a new name, beside the live database, checks it, and only then replaces the live one if you really need to.

In short: copy the .bak file into SQL Server's Backup folder, restore it as a new database such as Billing_Restore, open it and check the last bill, and only then decide what to replace.

Step 1: find the right .bak file

  • Look at the file's date and size. A backup much smaller than the ones before it is a warning sign.
  • If your backups are named by date, pick the newest one from before the problem started. After ransomware or a wrong mass update, that may be a few days back.
  • Copy the file to a local disk on the SQL Server PC. Restoring straight from a pen drive or a network share is slower and often fails on permissions.

The easiest place is SQL Server's own Backup folder, because the SQL Server service can always read it. For SQL Server 2022 Express it is usually:

C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup

(MSSQL15 is SQL Server 2019, MSSQL14 is 2017. If your instance has another name, that part of the path changes too.)

Step 2: check what is inside the backup

One .bak file can hold more than one backup, and it remembers the names of its data and log files. Open SQL Server Management Studio (SSMS), click New Query and run:

RESTORE HEADERONLY FROM DISK = N'C:\...\Backup\Billing.bak';
RESTORE FILELISTONLY FROM DISK = N'C:\...\Backup\Billing.bak';
  • HEADERONLY lists each backup in the file with its date (BackupFinishDate) and its Position. If there are several, note the position of the one you want.
  • FILELISTONLY lists the logical names of the data and log files, for example Billing and Billing_log. You need these in Step 3B.

Step 3A: restore with SSMS (the screens)

  1. In SSMS, right-click Databases and choose Restore Database...
  2. Under Source, pick Device, click ..., then Add, and choose your .bak file.
  3. Under Destination, change the database name to a new name, such as Billing_Restore. Do not leave the live name here.
  4. Open the Files page and tick Relocate all files to folder. Check that the file names shown now end in Billing_Restore.mdf and Billing_Restore_log.ldf, not the live database's file names.
  5. Leave the Options page as it is. Do not tick "Overwrite the existing database" for a side-by-side restore.
  6. Click OK. When it says the restore finished, the new database appears in the list.

Step 3B: restore with one command (no SSMS needed)

SSMS is not always installed on the office PC. The sqlcmd tool that comes with SQL Server does the same job. Open Command Prompt as administrator and run (one line):

sqlcmd -S .\SQLEXPRESS -E -Q "RESTORE DATABASE [Billing_Restore] FROM DISK = N'C:\...\Backup\Billing.bak' WITH MOVE N'Billing' TO N'C:\...\DATA\Billing_Restore.mdf', MOVE N'Billing_log' TO N'C:\...\DATA\Billing_Restore_log.ldf', RECOVERY, STATS = 10"
  • -S .\SQLEXPRESS is the SQL Server on this PC with the instance name SQLEXPRESS. Use -S . for a default instance.
  • -E signs in with your Windows account, which must be allowed to create databases on that SQL Server.
  • The two MOVE lines use the logical names from Step 2 and give the new database its own file names, so the live files are not touched.
  • If the file holds several backups, add FILE = 2, (the position from Step 2) after WITH.
  • STATS = 10 prints progress every 10 percent.

Step 4: check the restored copy

A restore that finished without an error is a good start, not proof. Check what is inside:

DBCC CHECKDB (N'Billing_Restore') WITH NO_INFOMSGS;

If SSMS shows only "Commands completed successfully", no damage was found. Red error lines mean the copy is damaged; try an older backup. Then look at the data itself: find the last bill or voucher in the restored database and see that its date and number are what you expect. If your software can be pointed at another database name, open the restored copy in it and look at a few reports.

Step 5: only if needed, replace the live database

If the live database is damaged and the restored copy is good, there are two safe ways:

  • Ask your software provider to point the software at Billing_Restore. Nothing is overwritten, and the old database stays for checking.
  • Or restore over the live database, using the steps below.

To restore over the live database:

  1. Take a fresh backup of the live database first, even if it is damaged. It may hold today's entries you will want later. If the backup stops with an error because of the damage, stop the SQL Server service and copy the database's .mdf and .ldf files to another folder instead, then start the service again.
  2. Close the software on every PC in the office, and close any other SSMS windows.
  3. Click New Query and run all of this together, in one go:
USE master;
ALTER DATABASE [Billing] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [Billing] FROM DISK = N'C:\...\Backup\Billing.bak'
    WITH REPLACE, RECOVERY, STATS = 10;
ALTER DATABASE [Billing] SET MULTI_USER;

USE master matters: if your own query window is inside Billing, the restore fails because you are the one using it. REPLACE tells SQL Server you really mean to overwrite the database, so check the file name twice before you run it.

Common restore errors and what they mean

Error message (short) What it means What to do
Operating system error 5 (Access is denied) The SQL Server service cannot read the folder the .bak is in. Copy the file into SQL Server's Backup folder (Step 1) and restore from there.
Exclusive access could not be obtained because the database is in use (3101) Someone, or your own query window, still has the database open. Close the software on every PC and other SSMS windows, and run the Step 5 script with USE master at the top.
The backup set holds a backup of a database other than the existing database (3154) You are restoring onto a database with another name. Restore under a new name instead. Use REPLACE only if you are sure.
The database was backed up on a server running version ... incompatible with this server (3169) The backup came from a newer SQL Server than this one. Install the same or a newer SQL Server version. A backup never restores onto an older version.
The media family on device is incorrectly formed (3241) The file is incomplete or damaged, or it came from a newer version. Try an older backup, and check the version as above.
Would exceed your licensed limit of 10240 MB per database (1827) SQL Server Express allows up to 10 GB of data per database. Restore on a paid edition, or the free Developer edition for checking only.

Restoring on a new PC

If the old PC is dead, install SQL Server on the new PC first: the same version or newer, and with the same instance name if your software expects one (for example SQLEXPRESS). Then restore as above.

If your software signs in to SQL Server with its own user name and password (a SQL login), that login lives in SQL Server, not in the backup. Create it again on the new PC, then link it to the restored database with:

CREATE LOGIN [billing_user] WITH PASSWORD = N'the same password as before';
USE [Billing];
ALTER USER [billing_user] WITH LOGIN = [billing_user];

SQL logins also need SQL Server set to "SQL Server and Windows Authentication mode" (right-click the server in SSMS, Properties, Security, then restart the service).

Your software provider can tell you the login name.

Common questions

How long does a restore take?

Roughly as long as the backup took, sometimes a little longer. A 5 GB database on an ordinary office PC usually restores in a few minutes. STATS = 10 shows the progress.

Can I restore just one table or one bill?

Not directly. Restore the whole backup under a new name, as in this guide, then copy the rows you need from the restored copy into the live database. That needs care with linked tables, so ask your software provider.

Can I restore a backup from SQL Server 2022 on SQL Server 2019?

No. A backup restores only on the same version or a newer one. Install the newer version on the PC you are restoring to.

Do I need to stop SQL Server to restore?

No. SQL Server stays running. Only the database you are replacing must not be in use; a side-by-side restore under a new name needs no one to log out.

How do I know my backups will restore when I need them?

Restore one now, while nothing is wrong, using the steps above. Do it once a month. See how to check that a backup really restores.

How JS1 Vault does it

JS1 Vault is a managed backup made for Indian offices that run SQL Server based ERP and billing software, on Express or any other edition:

  • It backs up each database every evening and checks every copy, with no SQL Server Agent or scripts. See how to automate SQL Server Express backups if you prefer to do it yourself.
  • Copies are packed, encrypted and kept in storage in India, and locked so nothing on your PC can change or delete them.
  • Once a month a copy is restored on your PC under a new name and opened, the same check as in this guide, done for you, so you know it really comes back.
  • When you need a copy back, our technician restores it beside your live data, never over it.
  • An email every evening says all is safe, or tells you and us at once when something is wrong.

See how JS1 Vault backs up SQL Server, or start a free trial.


← Back to all posts