[thelist] MSSQL 2005 .bak file - extract data?

Steve Bigelow steve.bigelow at tkcsb.com
Tue Jun 2 09:27:28 CDT 2009


If the .bak file is truly a database backup, you'll need to restore the
database in SQL Server - I don't think there's any other way of making the
content of the .bak file meaningful. 

It's usually easiest to restore a database from SQL Server's Management
Studio. SQL Server will complain if the backup doesn't contain the log
file(s), but you can continue with the restore and get useable data less any
transactions pending in the log file. See SQL Server's Books Online for a
description of what you'll need -- search for the keywords "restore

Once you have the data in SQL Server, there are a number of ways to get the
data out to Excel or Access. For example, if your target is Excel, you can
use SQL Server Integration Services (SSIS) to export each table, to a
variety of output formats including to a native Excel document. If your
target is Access, you can export using SSIS, or you can launch Access, and
pull in the SQL Server data as "imported" tables, or as "linked" tables.
(From your description, I suspect you'd be better off importing the data
rather than linking to it.) When working with Access, I usually try to
"pull" the data from the source into Access rather than trying to "push" the
data from the source into Access; it just seems a little cleaner and easier
to repeat if I screw up.

Hope this helps.

Steve Bigelow
TKCSB Images
Longmont, CO

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of David Kaufman
Sent: Tuesday, June 02, 2009 7:41 AM
To: thelist at lists.evolt.org
Subject: Re: [thelist] MSSQL 2005 .bak file - extract data?

Hi Roger,

rogerharness at comcast.net wrote:
> 'Volters, 
> I realize I'm a little over my head with this, but does 
 > anyone have any suggestions on how I can extract data from
> an SQL 2005 .BAK file, for export into Excel/Access, in a
> relatively simple manner? 

No, not from their native db files or their binary backup format.  When 
we migrated evolt from MSSQL 2000, the only usable export we were able 
to import was a table-by-table save as... CSV.  Their other (tab, etc.) 
delimited export formats failed to preserve embedded line-breaks in text 
and/or other characters.  CSV worked like a charm though.

Also since we had more than (IIRC) 32,000 rows in some tables we weren't 
able to open or import the resulting csv files in Excel, but we were 
able to import them into mysql using perl with no problem.

> I'm trying to install MIcrosoft SQL Server 2005 on my system  
> so I can open this file up, but I'm struggling a bit to figure  
> out how to view the data, even after installing this software. 

I tried that too.  Never was able to get it to work.  I think we needed 
the binary logs too, but didn't have them.

> Are there any simple solutions to get to that data, other than  
> relying on a vendor to get the data to me in another format? 

Not that I know of.  Things may have improved between MSSQL 2000 and 
2005 but I'd tell the vendor you need the data in a non-proprietary 
format.  It's not an unreasonable request, IMO.



* * Please support the community that supports you.  * *

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt ! 

More information about the thelist mailing list