[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to restore a backup to a server

Posted on 2011-03-17
10
Medium Priority
?
353 Views
Last Modified: 2012-05-11
I backed up the following 2 databases:

aspnetstockPro and stockselectsql to

F:\SQLServerBackups\aspnetstockpro_new.bak
and
F:\SQLServerBackups\stockselectsql_new.bak

Then I reinstalled SQL Server 2005. After doing this I had no databases on the server except  
System Databases and Database Snapshots.

I need to restore aspnetstockPro and stockselectsql to the server.
They should appear on the Databases list.

I tried to execute the following SQL statement to restore aspnetstockpro :

restore database aspnetstockpro
from disk = 'F:\SQLServerBackups\aspnetstockpro_new.bak' with replace,
move 'aspnetstockpro' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\aspnetstockpro.MDF',
move 'aspnetstockpro_Log' to 'C:\Microsoft SQL Server\Data\aspnetstockpro.LDF'

Error:

Msg 3234, Level 16, State 2, Line 1
Logical file 'aspnetstockpro' is not part of database 'aspnetstockpro'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What do I need to do? Image of the server files Image of the backup directory
0
Comment
Question by:Dovberman
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 4

Expert Comment

by:LeDaouk
ID: 35155999
try attach database and locate the .mdf file: more faster , but keed the .ldf with th mdf in the same folder
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35156011
Try this and find out logcal names of your files:
RESTORE FILELISTONLY from disk = 'F:\SQLServerBackups\aspnetstockpro_new.bak'

Then Restore:
RESTORE DATABASE aspnetstockpro
FROM DISK = 'F:\SQLServerBackups\aspnetstockpro_new.bak',
MOVE '<logical mdf file name>' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\aspnetstockpro.MDF',
MOVE '<logical log file name here>' to 'C:\Microsoft SQL Server\Data\aspnetstockpro.LDF'

You don't need replace because, there's nothing to replace ;)
0
 
LVL 25

Expert Comment

by:slam69
ID: 35156404
Or just right click on databases select restore and then use the gui?

enter the name of teh NEW database ie name of teh old you are now creating from teh backup

hit teh radio button next to from device and hit the ...

frind your file and hit ok then put a check in the box underneath restore in teh lower now populated window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Dovberman
ID: 35156773
Daniel PL

Thanks, I found the logical names.

I tried the following:

RESTORE DATABASE aspnetstockpro
FROM DISK = 'F:\SQLServerBackups\aspnetstockpro_new.bak',
MOVE 'aspnetdb' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\aspnetstockpro.MDF',
MOVE 'aspnetdb_log' to 'C:\Microsoft SQL Server\Data\aspnetstockpro.LDF'

Error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'aspnetdb'.

Can you help with the syntax error?

Thanks,
0
 
LVL 25

Expert Comment

by:slam69
ID: 35156814
did you try the gui?
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35156907
I've eaten WITH, sorry


RESTORE DATABASE aspnetstockpro
FROM DISK = 'F:\SQLServerBackups\aspnetstockpro_new.bak'
WITH MOVE 'aspnetdb' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\aspnetstockpro.MDF',
MOVE 'aspnetdb_log' to 'C:\Microsoft SQL Server\Data\aspnetstockpro.LDF'

Open in new window

0
 

Author Comment

by:Dovberman
ID: 35157002
The error message indicates file not found.

Msg 5133, Level 16, State 1, Line 8
Directory lookup for the file "C:\Microsoft SQL Server\Data\aspnetstockpro.LDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 8
File 'aspnetdb_log' cannot be restored to 'C:\Microsoft SQL Server\Data\aspnetstockpro.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 8
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.

I will search for  Data\aspnetstockpro.LDF

Perhaps the original backup did not create a log file.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35157017
Make sure SQL Server service account has access to C:\Microsoft SQL Server\Data\ folder and check that folder exist.
0
 

Author Comment

by:Dovberman
ID: 35160221
I finally got it.

RESTORE DATABASE aspnetstockpro
FROM DISK = 'F:\SQLServerBackups\aspnetstockpro_new.bak'
WITH REPLACE

Thanks,
0
 

Author Closing Comment

by:Dovberman
ID: 35160229
Thank you.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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