hologos2357
asked on
trying to restore .bak with SQL Server Management Studio
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.
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.
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).
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
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).
ASKER
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.
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
Msg 3156, Level 16, State 3, Line 1
File 'mydb_Log' cannot be restored to 'F:\SQL_logs\mydb2_log.ldf
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.
Run
RESTORE FILELISTONLY
FROM DISK = 'c:\mydb7.7.2006.bak'
and find the ouput
RESTORE FILELISTONLY
FROM DISK = 'c:\mydb7.7.2006.bak'
and find the ouput
ASKER
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-00 0000000000 0 0 0 512 1 NULL 34788000000026900007 EB29180A-84E2-4330-B757-67 5CAEA7EC03 0 1
mydb_Log F:\SQL_logs\mydb_log.ldf L NULL 2359296 314572800 2 0 0 00000000-0000-0000-0000-00 0000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-00 0000000000 0 0
mydb_Data E:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb.mdf
mydb_Log F:\SQL_logs\mydb_log.ldf L NULL 2359296 314572800 2 0 0 00000000-0000-0000-0000-00
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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::Validat eTargetFor Creation' 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::Validat eTargetFor Creation' 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.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::Validat
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::Validat
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.
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
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
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.m df',
MOVE 'mydb_Log' TO 'C:\pathICanWriteTo\mydb_l og.ldf'
RESTORE DATABASE mydb
FROM DISK = 'c:\mydb7.7.2006.bak'
WITH RECOVERY,
MOVE 'mydb_data' TO 'C:\pathICanWriteTo\mydb.m
MOVE 'mydb_Log' TO 'C:\pathICanWriteTo\mydb_l
ASKER
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!
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!
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.
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.
ASKER
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
Thanks,
James
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.]
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.]
ASKER
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!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Imran__fast-
Thanks. I changed the user from Network Service to Local Service and this did the trick.
Thanks. I changed the user from Network Service to Local Service and this did the trick.
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