Overview
This guide shows how to restore a .bak file into SQL Server 2022 or SQL Server Express using SQL Server Management Studio (SSMS) only. No T-SQL required.
Prerequisites
- SSMS installed with a login that has restore rights
- The .bak file must be accessible from the SQL Server machine - if the file is on your workstation and SQL Server is remote, copy the .bak to a folder on the server (example:
C:\Backups) and ensure the SQL Server service account has read permission to that folder - Know whether you are restoring to a new database name or overwriting an existing one
Part A - Restore to a New Database
- Open SSMS and connect to your SQL instance
- In Object Explorer, right-click Databases and select Restore Database...
- Under Source, select Device, click the ... button, then click Add, browse to the .bak file, click OK, and ensure the correct backup set is checked
- Under Destination, type the new database name in the Database field (example:
MyApp_Prod) - Click the Files page on the left
- If you want to control where the .mdf and .ldf files will be placed, check Relocate all files to folder and set the Data and Log folders - otherwise review the Restore As paths to avoid file name conflicts
- Click the Options page and confirm the following:
- Recovery state is set to RESTORE WITH RECOVERY (default) so the database comes online after restore
- Overwrite existing database is unchecked since this is a new database name
- Optionally check Close existing connections only if the name already exists and has active connections
- Click OK to start the restore and wait for the success message
- Expand Databases and verify your new database appears - right-click it and select Properties > Files to confirm file locations
- If the application uses SQL logins, map or create any needed logins under Security > Logins and ensure database users exist with proper roles
Part B - Overwrite an Existing Database
WARNING: This will permanently replace all current data in the target database with the contents of the backup. Ensure you are comfortable proceeding before continuing.
- In SSMS, right-click the database you want to overwrite and select Tasks > Restore > Database...
- Under Source, select Device, click ..., add the .bak file, and choose the backup set to restore
- Under Destination, confirm the Database name is the one you are overwriting
- Click the Options page and configure the following:
- Check Overwrite the existing database (WITH REPLACE)
- Check Close existing connections to the destination database
- Set Recovery state to RESTORE WITH RECOVERY unless you plan to apply additional differential or log backups
- Click the Files page if you need to adjust file locations - ensure the Restore As paths point to valid data and log folders and do not conflict with other databases
- Click OK to restore - after completion, refresh the database node and verify tables, views, and permissions
Common Issues
- Access denied or file not found: Confirm the .bak is on the server and the SQL Server service account has read permission to that folder
- File name conflicts: Use the Files page to edit the Restore As file names or check Relocate all files to folder
- Multiple backup sets: Choose the latest full backup you intend to restore - if you have differential or log backups, restore them in order using WITH NORECOVERY until the last one
- Version differences: You can restore from older to newer SQL Server versions but not vice versa - collation changes require additional steps after restore
- Orphaned users: After restore, fix any orphaned logins by creating or mapping the login and updating user mapping under Database > Security > Users
Default Data Folder Locations
- SQL Server Express 2022 default data path example:
C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA - Your path may differ based on instance name and installation location
Quick Checklist Before Restoring
- .bak file copied to server and readable by SQL Server service account
- Restore Database wizard launched from SSMS
- Correct backup set selected
- Destination database name confirmed
- Files page reviewed to avoid conflicts
- Options page set to RESTORE WITH RECOVERY and Overwrite only when intended
- Restore completed and database visible in Object Explorer
