Sql-server – How to extract data from an MSDE .dat backup file

sql serversql-server-2005

A client wants me to extract customer data from his old system The application he was using appears to have used MSDE as the database and its backups have the extension .dat

SSMS express edition or Access do not appear to have a way to import data from these files.
Can someone point me in the right direction.

Edit

OK Sorry for asking this question as I have just managed to find the solution to.

1: create a new database in sql server 2005

2: select the option to restore

3: Find the .dat file using the dialog, you'll get the message "The backup set holds a backup of a database other than the existing database" (this was the problem for me)

4: In options select "overwrite existing database

The msde .dat file is then imported into sql server

Best Answer

Check thats its a valid MSSQL backup by listing its contents;

RESTORE FILELISTONLY FROM DISK='c:\your.dat'

If ok, note the logical names of the .mdf & .ldf from the results, then;

RESTORE DATABASE MyTempCopy FROM DISK='c:\your.dat'
WITH 
   MOVE 'LogicalNameForTheMDF' TO 'c:\MyTempCopy.mdf',
   MOVE 'LogicalNameForTheLDF' TO 'c:\MyTempCopy_log.ldf'

To restore the backup to the new database MyTempCopy.

Related Solutions

MSDB Backup – Recovering a Specific Job Definition from an MSDB Backup

No need to restore to a scratch server, you can restore it to something like you say (MSDB_old) and do a query to get your job back:

USE msdb_old
SELECT * FROM sysjobs
JOIN sysjobsteps ON sysjobs.job_id=sysjobsteps.job_id
WHERE sysjobs.NAME='My Lost Job'
ORDER BY sysjobsteps.step_id

You'll have to restore

the entry in sysjobs
each entry in sysjobsteps related to the above entry in sysjobs
entries in sysjobhistory if you want history back
entries in sysjobschedules to get your schedules back

EDIT: Here's a script that should do it in SQL 2005 and 2008 (assuming your job was called "My Lost Job" and you restored to MSDB_Old)

DECLARE @JobID UNIQUEIDENTIFIER
SELECT @JobID = job_id FROM msdb_old.dbo.sysjobs WHERE NAME='My Lost Job'

INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_old.dbo.sysjobs
WHERE job_id=@JobID

INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_old.dbo.sysjobsteps
WHERE job_id=@JobID

SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
    (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
     [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
     operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
    instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
    [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
    operator_id_netsent,operator_id_paged,retries_attempted,[server]
FROM msdb_old.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF

INSERT msdb.dbo.sysjobschedules
SELECT * FROM msdb_old.dbo.sysjobschedules
WHERE job_id=@JobID

Sql-server – Import database: SQL Server 2005 Express to SQL Server 2008 Enterprise

yes you can backup db from sql server 2005 and restore it to sql server 2008. also you can import db from sql server 2005 in sql server 2008. for this create a new db on sql server 2008, then press right click of mouse on the name of created db. after that from task menu press Import Data... . in this step you can specify your source and destination dbs.

Best Answer

Related Solutions

MSDB Backup – Recovering a Specific Job Definition from an MSDB Backup

Sql-server – Import database: SQL Server 2005 Express to SQL Server 2008 Enterprise

Related Topic