CODEWITHSUNDEEP
sql-server-2005
deotpit
deotpit

Reputation: 95

SQL Server 2005 cannot restore my backup

This is how I backup:

  1. From right click menu on "Diary" database I select: "Tasks" and then "Backup";

  2. At the "Back Up Database - diary" window I select:

    a. "diary" at the "Database" scrolling list;
    b. Radio button "Database" at "Backup component" area;
    c. "Disk" radion button at "Back up to" area" and then I press the "Add" button for a location on the disk;

  3. I select the "option" menu item from "Select a page" area and I select: "Back up to the existing media set" and "overwrite all existing backup sets";

OK and I get a message that says:

The backup of database 'diary' completed successfully

I try (unsuccessfully !) to restore that database as follows:

  1. From right click menu on "Diary" database I select: "Tasks" and then "restore" and "Database";
  2. In the "Restore Database - diary" window I select "diary" from the "Destination for restore" scrolling list;
  3. I select the "From device" radio button and in the "Specify Backup" window I click "Add" and from its location on the disk I select the diary.bak file I backup ed before;
  4. I select "Options" menu item and I select "Overwrite the existing database" radio button;

  5. in the "Original file name" list I see 2 files:

    a. diary-------c:\program files\Microsoft SQL Server\MSSQL 3\MSSQL\DATA\diary.mdf
    b. diary-------c:\program files\Microsoft SQL Server\MSSQL 3\MSSQL\DATA\diary_log.ldf

  6. For the first item I select the location where I backed up diary.bat on disk (d:\diary\test.bak) and I leave the second item as is;

When I press OK I get the error message I cited before;

I'm really appreciate your patience and willingness to help me with a problem I thought I might forget about my old backups.

Thanks a lot !

Upvotes: 2

Views: 236

Answers (1)

marc_s
marc_s

Reputation: 755381

You're confusing your SQL Server data / transaction log files (.mdf and .ldf) with your backup (.bak).

To restore, select your .bak file - as you already do.

But you then also need to define where to put the resulting .mdf and .ldf file in your restore step #5 - like defining where to extract your files from a ZIP archive (the backup file is almost like a ZIP file, containing the data and transaction log files).

But do not use the same path / file name as your backup file here! That would essentially try to overwrite your backup (from which you're trying to restore the database) with the data file that is being restored!

Leave those two items in step #5 alone - provided those target directories where they would end up in do in fact exist (if they don't exist - then and only then do you need to make any changes here!)

Upvotes: 1

Related Questions