How to restore a database in SQL 2005 from a backup copy in SQL 2000?

  • judyhou

    SSC Veteran

    Points: 247

    Hi - I had a database in SQL 2000 and want to move it to SQL 2005. I made a backup copy in SQL 2000. When restoring it the SQL 2005, I receive message stating that it does not recognize the format. Any tips on this? Thanks.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Is this a native SQL backup from SQL Server 2000 you are trying to restore to SQL Server 2005?

  • judyhou

    SSC Veteran

    Points: 247

    Yes, I use the SQL client tool (equivalent to SQL Server management Studio?) to make the backup. However, the backup file does not have an extention of ".bak" (it does in 2005/2008).

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Backup extension is irrelevant, you can just use the restore GUI to restore the DB, or RESTORE DATABASE T-SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • judyhou

    SSC Veteran

    Points: 247

    Well, I use the studio UI to restore it - why 2005 does not recognize the 2000 backup file?

  • Lynn Pettis

    SSC Guru

    Points: 442118

    judyhou (6/1/2012)


    Yes, I use the SQL client tool (equivalent to SQL Server management Studio?) to make the backup. However, the backup file does not have an extention of ".bak" (it does in 2005/2008).

    What SQL client tool? Did it do the the backup or did it run a native SQL Server backup?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    judyhou (6/1/2012)


    Well, I use the studio UI to restore it - why 2005 does not recognize the 2000 backup file?

    What do you mean by 'not recognise'?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This was removed by the editor as SPAM

  • laskozinin

    SSC Journeyman

    Points: 77

    If you copied the file while the SQL Server was running this file probably will not attach either and you will need to get a valid backup of the database. Download help tool for sql database - SQL Server Restore Toolbox. http://www.sqlserver.restoretools.com/[/url] You can read up more on SQL Server here. http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/498-creating-a-new-database-using-an-mdf-file?_=1416149856104

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply