How to view and restore sql server backup file? Microsoft documents T-SQL statements that read a backup without restoring it. RESTORE HEADERONLY returns the backup header, RESTORE FILELISTONLY lists the database and log files inside, and RESTORE VERIFYONLY checks that the backup is complete and readable. To restore, use Restore Database in SQL Server Management Studio (SSMS), or the RESTORE DATABASE statement in T-SQL.
How to View and Restore SQL Server Backup File Step by Step
The order below is ours. Microsoft documents each statement on its own page and does not publish one combined procedure.
Step 1: View the Backup Header
Microsoft's RESTORE HEADERONLY page says the statement returns the backup header information for every backup set on a backup device, one row per backup. Its example reads a disk file:
RESTORE HEADERONLY
FROM DISK = N'C:\AdventureWorks-FullBackup.bak';
GO
Useful columns include BackupName, BackupTypeDescription, DatabaseName, ServerName, BackupSize, BackupStartDate, BackupFinishDate and RecoveryModel. The IsDamaged column is 1 when the database was damaged at backup time and the backup continued despite errors. If the backup set is password protected and you do not supply the password, BackupName shows Password Protected and the other columns are NULL.
Microsoft says the statement requires CREATE DATABASE permission. It scans every backup set on the media, so use FILE = a backup set number for a quick look at a single backup.
Step 2: List the Files Inside the Backup
Microsoft's RESTORE FILELISTONLY page says it returns the database and log files in a backup set, one row per file. Its example lists the second backup set on a device:
RESTORE FILELISTONLY FROM AdventureWorksBackups
WITH FILE=2;
GO
The LogicalName and PhysicalName columns give the file names. Type shows D for a data file or L for a log file. Note the logical names, because a restore to a new location needs them.
Step 3: Verify the Backup Before You Restore
Microsoft's RESTORE VERIFYONLY page says the statement checks that the backup set is complete and the entire backup is readable, without restoring it.
RESTORE VERIFYONLY FROM DISK = 'D:\AdventureWorks.bak';
GO
It also checks some page header fields, the checksum if present, and whether the destination devices have enough space. Microsoft states that it does not try to verify the structure of the data in the backup.
Step 4: Restore With SSMS
Microsoft's SSMS restore guide gives these steps:
- In Object Explorer, connect to the instance and expand it.
- Right-click Databases and select Restore Database.
- Under Source, choose Device if the backup came from another server, select the browse button, choose a backup media type such as File, click Add and select the .bak file.
- Under Destination, change the Database name if you want to restore under a different name.
- Under Restore to, keep To the last backup taken or choose Timeline for a point in time.
- Select the backup sets to restore.
- Optionally, open Files and set a new location for each file.
- On the Options page, choose the restore options and the recovery state, then select OK.
Recovery State Options
- RESTORE WITH RECOVERY is the default. It makes the database ready for use, and no more logs can be restored.
- RESTORE WITH NORECOVERY leaves the database unusable so more transaction logs can be restored.
- RESTORE WITH STANDBY leaves the database read-only and saves undo actions in a standby file.
Step 5: Restore With T-SQL to a New Location
Microsoft's restore to a new location page says files restore to their original locations by default, and MOVE relocates each file:
RESTORE DATABASE MyAdvWorks
FROM AdventureWorks2022_Backup
WITH RECOVERY,
MOVE 'AdventureWorks2022_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf',
MOVE 'AdventureWorks2022_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf';
GO
Use the logical names that RESTORE FILELISTONLY returns for your backup. Microsoft says to run RESTORE VERIFYONLY with the same MOVE clauses first, to check for sufficient space and file collisions.
Errors and Cautions From Microsoft
- Restoring a different database over an existing one fails without WITH REPLACE.
- The restore can fail if other connections are open, unless you select Close existing connections.
- An encrypted database needs the certificate or key that encrypted it, and Microsoft says to keep it as long as you keep the backup.
- Microsoft says not to restore databases from unknown sources without running DBCC CHECKDB on a nonproduction server first.
What Do These Sources Not Say?
- Microsoft's pages do not describe opening a .bak file in a text editor or viewing its data without SQL Server.
- They do not say how to repair a damaged backup file.
Frequently Asked Questions
Can I see what is inside a .bak file before restoring it?
Yes. Microsoft documents RESTORE HEADERONLY for the backup header and RESTORE FILELISTONLY for the files.
Does RESTORE VERIFYONLY prove my data is intact?
No. Microsoft says it does not verify the structure of the data in the backup.
Key Takeaways
- Use HEADERONLY, FILELISTONLY and VERIFYONLY to inspect a backup first.
- Restore through SSMS Restore Database or the T-SQL RESTORE DATABASE statement.
- Use MOVE to restore to a new location or name.
- VERIFYONLY checks readability, not data structure.
Sources and References
Facts follow Microsoft Learn.
- RESTORE HEADERONLY (Microsoft Learn) learn.microsoft.com
- RESTORE FILELISTONLY (Microsoft Learn) learn.microsoft.com
- RESTORE VERIFYONLY (Microsoft Learn) learn.microsoft.com
- Restore a Full Database Backup Using SSMS (Microsoft Learn) learn.microsoft.com
- Restore a Database to a New Location (Microsoft Learn) learn.microsoft.com