Opening a SQL Server bak file Not restoring

Question

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

User · Answer

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    -

User · Answer

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

User · Answer

From SQL Server 2008 SSMS  SQL Server Management Studio   simply    Connect to your database instance  for example   localhost sqlexpress   Either    a  Select the database you want to restore to  or  alternatively b  Just create a new  empty database to restore to   Right-click  Tasks  Restore  Database Device         Add  Browse to your  bak file Select the backup  Choose  overwrite Y  under options  Restore the database 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

User · Answer

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

User · Answer

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

[sql-server] Opening a SQL Server .bak file (Not restoring!)

Examples related to sql-server

Examples related to sql-server-2008

Examples related to backup

Examples related to restore