Opening a SQL Server .bak file (Not restoring!)

MicBehrens picture MicBehrens · Dec 13, 2011 · Viewed 113.7k times · Source

I have been reading a LOT of google posts and StackOverflow questions about how to restore a database in SQL Server from a .bak file.

But none of them states how to just READ the tables in the database-backup. (None that I could find anyway?)

I just want to check out some old information which now has been deleted, without actually restoring the full database. Is this possible?

.

EDIT:

I just wanted to post my T-SQL solution to the problem, so others may use it and I can go back and look it up ;)

First I created a new database called backup_lookup and took it offline. After this I could restore my old database mydb to the new one, without ever touching my original.

USE master
GO
RESTORE DATABASE backup_lookup
 FROM DISK = 'D:\backup\mydb.bak'
WITH REPLACE,
 MOVE 'mydb' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\backup_lookup.mdf',
 MOVE 'mydb_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\backup_lookup_log.ldf'
GO

I hope this helps :)

Answer

paulsm4 picture paulsm4 · Dec 13, 2011

From SQL Server 2008 SSMS (SQL Server Management Studio), simply:

  1. Connect to your database instance (for example, "localhost\sqlexpress")
  2. Either:

    • a) Select the database you want to restore to; or, alternatively
    • b) Just create a new, empty database to restore to.
  3. Right-click, Tasks, Restore, Database

  4. Device, [...], Add, Browse to your .bak file
  5. Select the backup.
    Choose "overwrite=Y" under options.
    Restore the database
  6. It should say "100% complete", and your database should be on-line.

PS: Again, I emphasize: you can easily do this on a "scratch database" - you do not need to overwrite your current database. But you do need to RESTORE.

PPS: You can also accomplish the same thing with T-SQL commands, if you wished to script it.