Solved

How to restore a backup to a server

Posted on 2011-03-17
10
349 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

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 500 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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 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