Click here to Skip to main content
15,947,389 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a sql express 2005 database which was created on another server.i want to restore that database to my sql server standard 2005 but i have so far have been unsuccessful. i tried the GUI restore option it failed so i followed the query guide from this website http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40841[^]
followed the instructions but still could not restore my database..

here is the Query
SQL
RESTORE FILELISTONLY
FROM DISK = 'E:\bari\latestDtNEt\millat_tractors_LTD.bak'


result
VB
millat_tractors C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD.mdf    D   PRIMARY 41943040    35184372080640  1   0   0   88142C8F-19A6-4013-86C0-84970A84B80E    0   0   41287680    512 1   NULL    20000000034100037   34F0C4FF-F9D0-467D-B9AF-652CA99F2C70    0   1
millat_tractors_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD_1.ldf  L   NULL    48758784    2199023255552   2   0   0   D9911775-121F-4B01-A7C0-21EABDF234B2    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1






restore query


SQL
RESTORE DATABASE millat_tractors_LTD
FROM DISK = 'E:\bari\latestDtNEt\millat_tractors_LTD.bak'
WITH REPLACE, MOVE 'millat_tractors' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD.mdf',
MOVE 'millat_tractors_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD_1.ldf'


and this the error i got
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'millat_tractors' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD.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\millat_tractors_LTD_1.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'millat_tractors_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD_1.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.


thankyou
Posted
Updated 28-Sep-16 8:14am

Check: C:\Program Files\Microsoft SQL Server is the same for the new server.
 
Share this answer
 
Comments
mubarakahmad 14-Jan-12 15:21pm    
am sorry i failed to understand your point. can you kindly please elaborate?
jinxster 14-Jan-12 20:46pm    
It appears your "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD.mdf" does not exist. On your original server it may have been backed up in another folder eg: "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\millat_tractors_LTD.mdf" or maybe someone changed the default install path to MS SQL Server or something.
mubarakahmad 15-Jan-12 9:20am    
yes you are right i have fixed it.i moved the data folder to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.. it gave me access denied option. later i went to sql server configuration manager-> services->properties of sqlserver->logon and changed from network services to local services . it fixed my problem and now i can restore my database .thankyou for your help
RESTORE DATABASE [C] FROM DISK = 'D:\\Inventory.bak' WITH RECOVERY,
MOVE 'Inventory_Data'
TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\c_Data.MDF',
MOVE 'Inventory_Log'
TO
'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\c_Log.LDF',
REPLACE, stats =1




Only thing you have to care, restore database is not created in MS SQL SERVER,
In My case After query run it should create new database with Name [C]
and create its file at path of [C:\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\]
 
Share this answer
 
v2
Comments
[no name] 28-Sep-16 14:21pm    
Why are you resurrecting FOUR year old already answered questions?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900