CODEWITHSUNDEEP
sql-serversql-server-2008backuprestore
PonyTricks
PonyTricks

Reputation: 1798

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

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 :)

Upvotes: 52

Views: 125822

Answers (5)

paulsm4
paulsm4

Reputation: 121881

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.

Upvotes: 36

PonyTricks
PonyTricks

Reputation: 1798

Just to add my TSQL-scripted solution:

First of all; add a new database named backup_lookup. Then just run this script, inserting your own databases' root path and backup filepath

USE [master]
GO
RESTORE DATABASE backup_lookup
 FROM DISK = 'C:\backup.bak'
WITH REPLACE,
 MOVE 'Old Database Name' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\backup_lookup.mdf',
 MOVE 'Old Database Name_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\backup_lookup_log.ldf'
GO

Upvotes: 2

David Smithers
David Smithers

Reputation: 2364

There is no standard way to do this. You need to use 3rd party tools such as ApexSQL Restore or SQL Virtual Restore. These tools don’t really read the backup file directly. They get SQL Server to “think” of backup files as if these were live databases.

Upvotes: 9

Nonym
Nonym

Reputation: 6299

It doesn't seem possible with SQL Server 2008 alone. You're going to need a third-party tool's help.

It will help you make your .bak act like a live database:

http://www.red-gate.com/products/dba/sql-virtual-restore/

Upvotes: 0

marc_s
marc_s

Reputation: 755471

The only workable solution is to restore the .bak file. The contents and the structure of those files are not documented and therefore, there's really no way (other than an awful hack) to get this to work - definitely not worth your time and the effort!

The only tool I'm aware of that can make sense of .bak files without restoring them is Red-Gate SQL Compare Professional (and the accompanying SQL Data Compare) which allow you to compare your database structure against the contents of a .bak file. Red-Gate tools are absolutely marvelous - highly recommended and well worth every penny they cost!

And I just checked their web site - it does seem that you can indeed restore a single table from out of a .bak file with SQL Compare Pro ! :-)

Upvotes: 12

Related Questions