• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

RESTORE DATABASE SYNTAX With log files

Hello Experts
I have extracted the database and now trying to restore the data to the new database which obviously has a different name
When I try to run the following statement I am getting ".mdf file cannot be over written"
Please can someoen advise?
Thanks

-- original datbase name is stagesng, new db name is stage2sng
use master
RESTORE DATABASE stage2sng
FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'

WITH REPLACE

Open in new window

0
newbie27
Asked:
newbie27
  • 10
  • 7
  • 6
  • +1
3 Solutions
 
HumpdyCommented:
RESTORE DATABASE stage2sng
FROM DISK='C:\sng\db\sngstage2\stagesng.bak'
WITH replace, move 'logical_name_for_data' TO 'c:\sqldata\\mydbname.mdf'
, MOVE 'logical_name_log' TO 'd:\sqllogs\mydbname.ldf'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you also need to put the MOVE clauses, to specify where each of the database files have to go.

to see the MOVE syntax, see the reference:
http://msdn.microsoft.com/en-us/library/ms186858.aspx

the issue is that during restore, without MOVE, the files will be restored to the location of the original database...
but you want to restore to a new database, which will NOT take the path locations of the existing db (if it exists)...
0
 
newbie27Author Commented:
thanks folks,

i have used the below and got the error attached

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'stage2sng' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


please can you advise?
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'stage2sng' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
newbie27Author Commented:
sorry, used the below

RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE 'stagesng' TO 'C:\sng\db\sngstage2\stagesng.mdf',
   MOVE 'stagesng_log' TO 'C:\sng\db\sngstage2\stagesng_log.LDF'
0
 
newbie27Author Commented:
AngelIII
:
I have also tried using the full path but getting the same error as above


RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE 'C:\sng\db\stagesng.mdf' TO 'C:\sng\db\sngstage2\stage2sng.mdf',
   MOVE 'C:\sng\db\stagesng_log.LDF' TO 'C:\sng\db\sngstage2\stage2sng_log.LDF'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does the folder:
C:\sng\db\sngstage2

exist? it has to, sql server restore will not create folders...
0
 
newbie27Author Commented:

Yes this folder do exist C:\sng\db\sngstage2
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, please confirm that there is no other database which uses already those file names (apart the one your restore over), and if you do confirm, please post the exact and full error message.
0
 
newbie27Author Commented:
Yes, stage2sng.mdf and stage2sng.ldf are the files which I want to create for new database, there is no such file already exist

RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE 'C:\sng\db\stagesng.mdf' TO 'C:\sng\db\sngstage2\stage2sng.mdf',
   MOVE 'C:\sng\db\stagesng_log.LDF' TO 'C:\sng\db\sngstage2\stage2sng_log.LDF'

I am getting this error

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'stage2sng' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
EvilPostItCommented:
Does the stage2sng  database already exist? You may want to do a refresh.

The backup set holds a backup of a database other than the EXISTING 'stage2sng' database.
0
 
newbie27Author Commented:
Hi,
Yes the database "stage2sng" already exist, which I have created just today and I want to restore data from the stagesng database. I hope this makes sense?
Thanks
0
 
EvilPostItCommented:
Either delete stage2sng or add replace like so....

RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE 'C:\sng\db\stagesng.mdf' TO 'C:\sng\db\sngstage2\stage2sng.mdf',
   MOVE 'C:\sng\db\stagesng_log.LDF' TO 'C:\sng\db\sngstage2\stage2sng_log.LDF', REPLACE

Bear in mind this will overwrite the database.
0
 
newbie27Author Commented:
Thanks I have tried the above

Msg 3234, Level 16, State 2, Line 1
Logical file 'C:\sng\db\stagesng.mdf' is not part of database 'stage2sng'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

obviously, stagesng.mdf is not part of the new database....

why it would not letting me restore this to stage2sng database.... ????

thanks
0
 
HumpdyCommented:
logical file is the name of the data file, not the actual filename
if you write click on the database, go to properties, and go to files, you will see the name in there

it's probably stagesng.
0
 
EvilPostItCommented:
I see the problem. Below there are sections which i have marked LOGICAL FILE NAME. If you check the original database properties under files you will see logical fileaname. Its will be something like "stagesng_data" & "stagesng_log". This is what need to be entered.

RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE [LOGICAL FILE NAME]' TO 'C:\sng\db\sngstage2\stage2sng.mdf',
   MOVE '[LOGICAL FILE NAME]' TO 'C:\sng\db\sngstage2\stage2sng_log.LDF', REPLACE
0
 
HumpdyCommented:
RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE 'stagesng' TO 'C:\sng\db\sngstage2\stage2sng.mdf',
   MOVE 'stagesng_logF' TO 'C:\sng\db\sngstage2\stage2sng_log.LDF', REPLACE
0
 
newbie27Author Commented:
OK, I have tried with this

RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE 'stagesng.mdf' TO 'C:\sng\db\sngstage2\stage2sng.mdf',
   MOVE 'stagesng_log.LDF' TO 'C:\sng\db\sngstage2\stage2sng_log.LDF', REPLACE

and this is the error

Msg 3234, Level 16, State 2, Line 1
Logical file 'stagesng.mdf' is not part of database 'stage2sng'. Use RESTORE FILELISTONLY to list the logical file names.
0
 
HumpdyCommented:
try this please

RESTORE DATABASE stage2sng
   FROM DISK = 'C:\sng\db\sngstage2\stagesng.bak'
   WITH MOVE 'stagesng' TO 'C:\sng\db\sngstage2\stage2sng.mdf',
   MOVE 'stagesng_log' TO 'C:\sng\db\sngstage2\stage2sng_log.LDF', REPLACE
0
 
HumpdyCommented:
in the move section you still had your file extensions so it was failing. correct syntax is as above (ensuring you got your logical names correct.
0
 
EvilPostItCommented:
look at the original database properties and have a look at the attatched screenshot. This outlines the logical name location.
dbprop.bmp
0
 
newbie27Author Commented:
Tried Humpdy, still getting the same error

Msg 3234, Level 16, State 2, Line 1
Logical file 'stagesng' is not part of database 'stage2sng'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
HumpdyCommented:
ok, then you have your logical name wrong, you need to look at the properties to get your logical names correct, as posted above by EvilPostIt
0
 
HumpdyCommented:
or you can use this query to get your logical file names for your data and transaction files.

select name from sys.sysfiles
0
 
EvilPostItCommented:
Or on the other hand do at the error message suggests and run the RESTORE FILELISTONLY command....
0
 
newbie27Author Commented:
EvilPostIt:

That is it ! thank you very much, I was using wrong logical names ...

When I run the backup, it created stagesng.mdf and stagesng.log files and I was using these file names, but when I looked into their original logical names they were something like sng6 and sng6_log

It looks like it has restored the db ...

thank you again for the help folks !
0
 
EvilPostItCommented:
No probs.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 10
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now