resore db

Question

Post reply

resore db

blahknow

Right there with Babe

Points: 773
More actions
June 18, 2010 at 11:11 pm

#145361

Hi everyone,
I have a "myBackup.bak" file on my hard disk. I backed it from a db called "abc". Now i want to restore it into a db called: "def".
From right-click menu on "def" i select "tasks" then "restore" and "myBackup.bak" from its' location on my hard disk.
I get an error message that says: "The backup set holds a backup of a database other than the exixting 'def' database'.
Other words: I can only restore it to its' original db.
Is there a way to resore a database to a location other then the original ?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jocelyn Anne Limchao SSC Eights! Points: 805 More actions · Answer 1

you can change the files under the restore as found in the options

blahknow Right there with Babe Points: 773 More actions · Answer 2

Thanks. I tried all options:

These are the options:

1. Overwrite the existing database.

Selecting this option, the database "abc" was removed and replaced by the backuped one. I wanted

db: "def" to be replaced and keep "abc" as is.

2.Preserve the application settings. I recieved an Error message!

3. Prompt before restoring each backup: Error message !

4. Restrict access to the restored db: Error !

Any other option ?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Type the name of the DB you want it restored as at the top of the restore dialog.

You're probably right-clicking the database 'abc' and selecting restore. Don't. Right click the database folder in object explorer and select restore.

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

blahknow Right there with Babe Points: 773 More actions · Answer 4

Thanks Gail.

Here is what i do to produce an error message in details:

1. Right "def" database, select "tasks" and then: "restore" and then: "database";

2. At "Restore database - def" window , at: "To database" combo box i select "def";

3. I select "From device" and then i click "Add" and then i select the file: "abc.bak" from its' location on my hard disk and i click "OK";

4. From a list of 10 backups that were made in various dates, I select the last one;

5. At "options" window i select: "Overwrite the existing database" and then i click "OK";

As a result I get an error message that says: "The file: 'c:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\a.mdf' cannot be overwritten. It is being used by database 'a'.

Is there anything i made above that is inconsistent with what you suggested ?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Go to the options tab and change the location of the files for the newly restored DB.

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

Bill Scott SSCrazy Points: 2338 More actions · Answer 6

try something like this

restore database def from disk = 'BAK file name'

with move 'DEF_Data' to 'DEF_New_data.MDF',

move 'DEF_Log' to 'DEF_New_log.ldf'

'BAK file name' = the fully qualified path to the existing .BAK file

'DEF_New_data.MDF' = the fully qualified path to the new database file

'DEF_New_log.ldf' = the fully qualified path to the new log file

change the DEF_New part to a file name that is not currently in use

Duncan Howe SSC Veteran Points: 216 More actions · Answer 7

Detach the "def" database first by right-clicking on it and selecting detach, and then use the restore script that Bill Scott posted.

If you specify a different path to the current "def" database files in the "MOVE" part of Bill's script, you will be able to re-attach your "def" database at a later date (if required, and as long as you remove the existing "def" database). Otherwise, you can just delete the database.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 8

Have you tried restoring as 'def2'?

And as a seperate question, your not trying to restore the backup on a lower version of sql are you? (i.e the .bak files isnt from ss2k8 andn your trying to restore to 2k5).

Adam Zacks-------------------------------------------Be Nice, Or Leave

blahknow Right there with Babe Points: 773 More actions · Answer 9

Hi Gail,

This is what i did.

I cleared off the SQLSERVER2005, opened the 2 folders on my disk and tried to drag

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\a_log.ldf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\a.mdf

To the folder where a.bak is located "d:\a\a.bak.

I couldnt't do that. An error message showed up saying:"Cannot copy a: it is being used by another person

or program..."

I turned on SQLSERVER2005, right clicked "b" and selected "tasks" and "restore" and "database".

At "to database" i assigned "b", at "from device" I selected d:\a\a.bak and "OK".

At "select the backups sets" I selected the newest of the list then i selected "options", "Overwrite

the existing database" and at "restore the database files as" i changed:

c:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\a.mdf" to:

"d:\b\a.mdf" and c:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\a.ldf" to

"d:\b\a.ldf". Selecting "OK" did not yield any error this time ! I switched to the b database to check

that it exists and there it was, living and kicking !! I was not dreaming !

You made my dream come true and i'm truely grateful to you for that !

Thanks a lot !

blahknow Right there with Babe Points: 773 More actions · Answer 10

Thanks Bill,

It turned out that changing the path of the "ldf" and "mdf" file was the remedy to my dismay. Having changed their pass from c:\Program files... to where the "bak" file is located solved the problem. I guess this was your suggestion but i followed Gail's instructions and that how my problem was solved.

Thanks a lot to you too !

blahknow Right there with Babe Points: 773 More actions · Answer 11

Hi Duncan,

Bill's way was the right way as well as Gail's. I did it "the Gail's way" and things came back to order.

Thanks a lot to you and all the members who tried to help me.

Thanks !