none
Error when restoring database to different server from .dat and .ldf

    Question

  • Hello,

    We are using SQL Server 2005. I was given a client's .dat and .ldf files and asked to restore it on our server. I ran the following sql statement:

    RESTORE
    DATABASE [WGAP]
    FROM
    DISK = 'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Backup\3rdPartyDBs\Womens\WGAP.dat'
    WITH
    MOVE
    'WGAP.dat' 
    TO
    'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Data\WGAP.dat',
    MOVE
    'WGAP_log.ldf'  
    TO
    'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Data\WGAP_log.ldf';

    This statement gave me the following error:
    Msg 3241, Level 16, State 0, Line 1
    The media family on device 'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Backup\3rdPartyDBs\Womens\WGAP.dat' is incorrectly formed. SQL Server cannot process this media family.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    I used this statement to restore bak files for clients, but I seem to be having trouble restoring from the .dat file. I would appreciate any help that you could provide.

    Thanks,
    Adam
    Wednesday, October 14, 2009 8:12 PM

Answers

  • Hi Adam

    I suspect that this is not a SQL backup file - from the error message it's not a valid backup file in any case.

    I don't recognise the .dat extension, but since you've been given a dat and a ldf, I would try to attach the files as a database. Perhaps that's what the client has given you (a detached database, not a backup).

    HTH

    Ewan



    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, October 14, 2009 8:42 PM

All replies

  • Hi Adam

    I suspect that this is not a SQL backup file - from the error message it's not a valid backup file in any case.

    I don't recognise the .dat extension, but since you've been given a dat and a ldf, I would try to attach the files as a database. Perhaps that's what the client has given you (a detached database, not a backup).

    HTH

    Ewan



    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, October 14, 2009 8:42 PM
  • do the following:

    restore

     

    headeronly from disk='C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Backup\3rdPartyDBs\Womens\WGAP.dat'

    If this file is a valid backup it will return information about that backup.

    This file could also be an mdf, you might want to try to attach a copy of it.


    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, October 15, 2009 11:23 AM
  • Hello Adam

    Clear me one thing...you got backup from SQL Server 6.5 ?
    SNIVAS
    Thursday, October 15, 2009 12:30 PM
  • Please confirm whether the .DAT file that yuou are talking about here is a SQL DATABASE BACKUP FILE (BAK) or SQL DATABASE DATA FILE (mdf).
    Based on that we can advice you which method you need to employ either RESTORE or ATTACH option.
    Thanks, Leks
    Thursday, October 15, 2009 9:23 PM
  • Thanks for the help. I was given the impression from the client that the .dat was actually a .bak file. I read your suggestion about trying to attach it in case it is a .mdf file instead. I was able to attach it using the following:

    exec sp_attach_db @dbname=N'WGAP',
    @filename1=N'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Data\WGAP.dat',
    @filename2=N'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Data\WGAP_log.ldf';

    Once again, thanks for the help.

    Monday, October 19, 2009 9:11 PM
  • Adam,

     

    I am having the exact same problem with lytec 2010.  Were you successful with 

     

    exec sp_attach_db @dbname=N'WGAP', 
    @filename1=N'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Data\WGAP.dat', 
    @filename2=N'C:\LytecData\MSSQL$LYTEC_SQL\MSSQL\Data\WGAP_log.ldf';

     

    and what was the result?

    did this create a .bak file able to be restored?

    Please help, Thanks!

    Sunday, November 21, 2010 1:51 AM