Solved

trying to restore .bak with SQL Server Management Studio

Posted on 2006-07-10
16
17,068 Views
Last Modified: 2013-12-03
I have a file located at c:\mydb.7.7.2006.bak.  I need to restore it to a location on my local computer running win xp with sql express.  

Here is what I have done:

restore filelistonly
from disk = 'c:\mydb7.7.2006.bak'

Here are the physical paths in the result:
Physical Name - mydb_data: E:\Program Files\Miscrosoft SQL Server\MSSQL\mydb.mdf
Physical Name - mydb_Log: F:\SQL_logs\mydb_log.ldf

Using this info, can someone tell me how to restore the .bak?

Thanks in advance.


0
Comment
Question by:hologos2357
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17074040
RESTORE FILELISTONLY
   FROM DISK = 'c:\mydb7.7.2006.bak'
RESTORE DATABASE myDb
   FROM DISK = 'c:\mydb7.7.2006.bak'
   WITH MOVE 'myDb' TO 'E:\Program Files\Miscrosoft SQL Server\MSSQL\mydb.mdf',
   MOVE 'myDb' TO 'F:\SQL_logs\mydb_log.ldf'
GO
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17074138
If the db still exists, with the files in exactly the same locations as before (as shown in the FILELISTONLY output), and you want to restore over the current db, do this:

RESTORE DATABASE mydb
FROM DISK = 'c:\mydb7.7.2006.bak'
WITH REPLACE, RECOVERY


If the db does not exist, or you want to restore to a different name, do this:

RESTORE DATABASE mydb
FROM DISK = 'c:\mydb7.7.2006.bak'
WITH RECOVERY,
    MOVE 'mydb_data' TO 'E:\Program Files\Miscrosoft SQL Server\MSSQL\mydb2.mdf',  --<<-- chg physical file name as desired
    MOVE 'mydb_Log' TO 'F:\SQL_logs\mydb2_log.ldf'  --<<-- chg physical file name as desired


As you'll recall, there is no requirement that the dataset name match the db name in any way; in fact you don't have to use the extensions .MDF and .LDF if you don't want to (although for recognizability and consistency it's certainly best to).
0
 

Author Comment

by:hologos2357
ID: 17074377
Thanks, both.  I get errors with each solution.  Do I need to create a blank SQL db over which I restore the .bak?  I don't have the original db on my local.

aneeshattingal -

I get the following error:
Msg 3234, Level 16, State 2, Line 3
Logical file 'mydb' is not part of database 'mydb'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

ScottPletcher-

I get the following error using the second solution:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\Program Files\Miscrosoft SQL Server\MSSQL\mydb2.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'mydb_Data' cannot be restored to 'E:\Program Files\Miscrosoft SQL Server\MSSQL\mydb2.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "F:\SQL_logs\mydb2_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'mydb_Log' cannot be restored to 'F:\SQL_logs\mydb2_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17074462
Run

RESTORE FILELISTONLY
FROM DISK =  'c:\mydb7.7.2006.bak'


and find the ouput

0
 

Author Comment

by:hologos2357
ID: 17074522
Hard to copy/paste, but here is all of it:

mydb_Data      E:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb.mdf      D      PRIMARY      148111360      330301440      1      0      0      00000000-0000-0000-0000-000000000000      0      0      0      512      1      NULL      34788000000026900007      EB29180A-84E2-4330-B757-675CAEA7EC03      0      1
mydb_Log      F:\SQL_logs\mydb_log.ldf      L      NULL      2359296      314572800      2      0      0      00000000-0000-0000-0000-000000000000      0      0      0      512      0      NULL      0      00000000-0000-0000-0000-000000000000      0      0
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 17074541
Oh, right, probably the current machine has only a c: drive (?).  If so, and there is space available on the c: drive to restore it, try this:


RESTORE DATABASE mydb
FROM DISK = 'c:\mydb7.7.2006.bak'
WITH RECOVERY,
    MOVE 'mydb_data' TO 'C:\mydb.mdf',
    MOVE 'mydb_Log' TO 'C:\mydb_log.ldf'

0
 

Author Comment

by:hologos2357
ID: 17074570
Yes, only  a C drive.  I tried the above and got this error:
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\mydb.mdf'.
Msg 3156, Level 16, State 5, Line 1
File 'mydb_Data' cannot be restored to 'C:\mydb.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\mydb_log.ldf'.
Msg 3156, Level 16, State 5, Line 1
File 'mydb_Log' cannot be restored to 'C:\mydb_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17074589
hologos2357,

RESTORE DATABASE myDb
   FROM DISK = 'c:\mydb7.7.2006.bak'
   WITH MOVE 'myDb' TO 'C:\mydb.mdf',
   MOVE 'myDb' TO 'C:\mydb_log.ldf'
GO
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17074637
Odd, looks like you -- that is, the id under which the command was running -- didn't have authority to write to the drive.  Is there a directory path you do have authority to write to?  If so, change the restore to point to that location:

RESTORE DATABASE mydb
FROM DISK = 'c:\mydb7.7.2006.bak'
WITH RECOVERY,
    MOVE 'mydb_data' TO 'C:\pathICanWriteTo\mydb.mdf',
    MOVE 'mydb_Log' TO 'C:\pathICanWriteTo\mydb_log.ldf'
0
 

Author Comment

by:hologos2357
ID: 17075048
Aneeschattingal-

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

ScottPletcher-
How do I change permissions to get authority to write to the drive?  I would think I already have it, but maybe I need to enable myself?

Thanks!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17075078
If you don't have authority to the drive, you probably don't have authority to give yourself that authority :-) .

You would need a Windows admin to grant you at least full authority to the C:\ drive, and even more permissions might be helpful to what you want to do.

0
 

Author Comment

by:hologos2357
ID: 17075389
Hmm, well this is my computer, and I am the only one who uses it.  I can't see why I couldn't write to my own drive?  Am I missing something here?  Do I need to call MS and get some licensing code?  I am using the free SQL express.  Will this not do the trick?

Thanks,
James
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17075440
Do you have full admin rights in SQL?

Does the SQL id have full admin rights to Windows / other resources?

[These qs may not apply to Express version; I haven't used it.]
0
 

Author Comment

by:hologos2357
ID: 17078277
Sorry, but I can't figure out how to determine if I have access or not.  During setup, I chose to use my Windows Authentication (I just reinstalled sql express plus the trial version of 2005).  How do I find out if I have full admin rights in sql, and how do I find out if the sql id has full adminr rights to windows?

Thanks!
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 17080781
>>Sorry, but I can't figure out how to determine if I have access or not.
On your machine go to start --> programfiles --> microsoft sql server 2005 --> configuration tools sql server configuration manager
user services check mssql server and check the log on tab and see what is the user there and give him full privellages on the system if it is local then the user u have used to login to the server should be admin.

and then
use this  scotts suggestion
RESTORE DATABASE mydb
FROM DISK = 'c:\mydb7.7.2006.bak'
WITH RECOVERY,
    MOVE 'mydb_data' TO 'C:\mydb.mdf',
    MOVE 'mydb_Log' TO 'C:\mydb_log.ldf'



0
 

Author Comment

by:hologos2357
ID: 17081439
Imran__fast-
Thanks.  I changed the user from Network Service to Local Service and this did the trick.  
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

863 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

22 Experts available now in Live!

Get 1:1 Help Now