?
Solved

RESTORE DATABASE SYNTAX With log files

Posted on 2010-11-08
26
Medium Priority
?
449 Views
Last Modified: 2012-05-10
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
Comment
Question by:newbie27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 6
  • +1
26 Comments
 
LVL 10

Assisted Solution

by:Humpdy
Humpdy earned 400 total points
ID: 34084943
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 34084944
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
 
LVL 8

Author Comment

by:newbie27
ID: 34085084
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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 8

Author Comment

by:newbie27
ID: 34085093
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
 
LVL 8

Author Comment

by:newbie27
ID: 34085146
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34085237
does the folder:
C:\sng\db\sngstage2

exist? it has to, sql server restore will not create folders...
0
 
LVL 8

Author Comment

by:newbie27
ID: 34085440

Yes this folder do exist C:\sng\db\sngstage2
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34085456
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
 
LVL 8

Author Comment

by:newbie27
ID: 34085481
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34085737
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
 
LVL 8

Author Comment

by:newbie27
ID: 34085867
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34085888
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
 
LVL 8

Author Comment

by:newbie27
ID: 34085928
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34085951
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34085954
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34085960
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
 
LVL 8

Author Comment

by:newbie27
ID: 34085986
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34085993
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34086000
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34086036
look at the original database properties and have a look at the attatched screenshot. This outlines the logical name location.
dbprop.bmp
0
 
LVL 8

Author Comment

by:newbie27
ID: 34086046
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34086056
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34086071
or you can use this query to get your logical file names for your data and transaction files.

select name from sys.sysfiles
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 1200 total points
ID: 34086084
Or on the other hand do at the error message suggests and run the RESTORE FILELISTONLY command....
0
 
LVL 8

Author Comment

by:newbie27
ID: 34086085
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34086096
No probs.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question