Solved

RESTORE DATABASE SYNTAX With log files

Posted on 2010-11-08
26
433 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility

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

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:Humpdy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Or on the other hand do at the error message suggests and run the RESTORE FILELISTONLY command....
0
 
LVL 8

Author Comment

by:newbie27
Comment Utility
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
Comment Utility
No probs.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

16 Experts available now in Live!

Get 1:1 Help Now