Solved

RESTORE DATABASE SYNTAX With log files

Posted on 2010-11-08
26
434 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
  • 10
  • 7
  • 6
  • +1
26 Comments
 
LVL 10

Assisted Solution

by:Humpdy
Humpdy earned 100 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 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
 
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 142

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 142

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 300 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now