We help IT Professionals succeed at work.

sql server 2005 enterprise edition restore database

rgb192
rgb192 asked
on
667 Views
Last Modified: 2012-05-08
using sql server management tools express edition
with sql server 2005 enterprise

RESTORE DATABASE  owsite FROM DISK = 'c:\backup\owsite_20091029.BAK'

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\owsites.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'owsites' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\owsites.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 "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\owsites_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 'owsites_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\owsites_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.
Comment
Watch Question

Commented:
Error indicates that the path is not valid. Please check if the path exists. Also, you need to use with move command along with restore database as below:

restore database <dbname> from disk = '<backup file location>'
with move '<datafile logical name>' to 'Storage location.mdf'
, move '<Logfile logical name>' to 'Storage location.ldf'
, stats = 1
, replace

Commented:
Here you go for the database you are trying to restore:

Restore database owsites from disk = 'c:\backup\owsite_20091029.BAK'
with move 'owsites' to 'C:\owsites.mdf'
, move 'owsites_log' to 'C:\owsites_log.ldf'
, stats = 1

You can replace the file locations (C:\owsites.mdf and C:\owsites_log.ldf) with your required disk locations.

Author

Commented:
thanks

works for this owsites database

but not on ownew database

Restore database ownew from disk = 'c:\backup\ownew_20091029.BAK'
with move 'ownew' to 'C:\sqlfiles\ownew.mdf'
, move 'ownew_log' to 'C:\sqlfiles\ownew_log.ldf'
, stats = 1



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

 

Commented:
ownew is not the right name of the database data file.

Please execute the command

restore filelistonly from disk = 'c:\backup\ownew_20091029.BAK'

Once you execute the above one, you will get the logical names under the 1st column.

Replace 'ownew' with the logical name of the data file and try executing the restore database command.

Author

Commented:
attached is results for
restore filelistonly from disk = 'c:\backup\ownew_20091029.BAK'

ownew_Data	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ownew_Data.mdf	D	PRIMARY	734003200	35184372080640	1	0	0	2ADA1A19-E2D5-4D7C-83F2-2678D56D46D8	0	0	577961984	512	1	NULL	286000001992000037	D48E1A3F-4EEA-4FD0-B629-AA1B271483EC	0	1
 
ownew_Log	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ownew_Log.ldf	L	NULL	938344448	2199023255552	2	0	0	D3527696-692A-4220-BE36-1AC2B8031116	0	0	0	512	0	NULL	0	00000000-0000-0000-0000-000000000000	0	1

Open in new window

Commented:
okey...use this

Restore database ownew from disk = 'c:\backup\ownew_20091029.BAK'
with move 'ownew_Data' to 'C:\sqlfiles\ownew.mdf'
, move 'ownew_log' to 'C:\sqlfiles\ownew_log.ldf'
, stats = 1

Author

Commented:
thanks -works
final database

restore filelistonly from disk = 'c:\backup\owsite_20091029.BAK'

owsites	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\owsites.mdf	D	PRIMARY	2097152	35184372080640	1	0	0	1A5D5678-9311-480C-A126-CFD1A9737A91	0	0	1310720	512	1	NULL	31000000011700070	BF48FADC-9E6D-49DD-BFE8-0C583C3615DD	0	1
owsites_log	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\owsites_log.ldf	L	NULL	3866624	2199023255552	2	0	0	BFC1CC96-3A0C-48B8-AB6D-19E545FA8525	0	0	0	512	0	NULL	0	00000000-0000-0000-0000-000000000000	0	1

Open in new window

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Restore database owsite from disk = 'c:\backup\owsite_20091029.BAK'
with move 'owsites' to 'C:\sqlfiles\ownew.mdf'
, move 'owsites_log' to 'C:\sqlfiles\ownew_log.ldf'
, stats = 1
Msg 1834, Level 16, State 1, Line 1
The file 'C:\sqlfiles\ownew.mdf' cannot be overwritten.  It is being used by database 'ownew'.
Msg 3156, Level 16, State 4, Line 1
File 'owsites' cannot be restored to 'C:\sqlfiles\ownew.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\sqlfiles\ownew_log.ldf' cannot be overwritten.  It is being used by database 'ownew'.
Msg 3156, Level 16, State 4, Line 1
File 'owsites_log' cannot be restored to 'C:\sqlfiles\ownew_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.

Open in new window

Author

Commented:
Restore database owsite from disk = 'c:\backup\owsite_20091029.BAK'
with move 'owsites' to 'C:\sqlfiles\owsite.mdf'
, move 'owsites_log' to 'C:\sqlfiles\owsite_log.ldf'
, stats = 1

works---thanks
It seems database named owsite  already there. So please use the replace while restoring the database

Author

Commented:
thanks

Commented:
rgb192 - Sorry that was my mistake...Thanks for correcting the code urself....much appreciated.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.