Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Restore to directory does not exist

Author  Topic 

winny86
Starting Member

7 Posts

Posted - 2013-06-13 : 05:02:05
Hey guys,

I was hoping somebody could shed a light on this for me:

I have developed an application is msbuild, that restores a .bak file for a given directory to my SQL Server instance using a default location.

A couple of days ago, my script worked perfectly, but now I get an error:

Files 'File' cannot be restored to: 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL11\MSSQL\DATA\File.mdf' - which doesnt exist on my computer.

A couple of days ago it was restoring to: 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\File.mdf' - which is the correct path.

Does anyone have an idea of why this might be happening?

Thanks,

Dan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 05:18:33
These both paths are not the same... check once
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL11\MSSQL\DATA\File.mdf' -- I think wrong path
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\File.mdf' -- Correct path

--
Chandu
Go to Top of Page

winny86
Starting Member

7 Posts

Posted - 2013-06-13 : 05:20:18
What I dont understand is why it is trying to restore the database to that path when it doesnt exist on my machine?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 05:27:06
check this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181968
http://stackoverflow.com/questions/3479725/restoring-a-backup-with-sql-server
http://www.techrepublic.com/blog/window-on-windows/how-do-i-restore-a-sql-server-database-to-a-new-server/454 -- Process for RESTORE database to different server
--
Chandu
Go to Top of Page

winny86
Starting Member

7 Posts

Posted - 2013-06-13 : 05:41:46
Thanks for the link,

I have just gone in to Management Studio and coded the restore statement up:

RESTORE DATABASE DBName FROM DISK = 'Location\Databases\DBName.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

I am now getting the following error:

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'Location\Databases\DBName.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The directory path definately exists on my machine.

Can you advise?

Thanks,

Dan
Go to Top of Page

winny86
Starting Member

7 Posts

Posted - 2013-06-13 : 05:50:35
Sorry I realised why I was getting the previous error.

Now when I try and restore from the .bak file, I get the following error:

Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL11\MSSQL\DATA\DBName.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 2
File 'BlinkBoxMaster' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL11\MSSQL\DATA\DBName.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL11\MSSQL\DATA\DBName.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 2
File 'BlinkBoxMaster_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL11\MSSQL\DATA\DBName.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Can anybody point me in the right direction as to why it is trying to restore to this particular directory please?

Thanks,

Dan
Go to Top of Page

winny86
Starting Member

7 Posts

Posted - 2013-06-13 : 06:00:41
I have just looked at the properties for my database server.

The Database default locations are down as:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

So I dont understand where it is getting: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL1\MSSQL\DATA.

Dan
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 06:04:22
try RESTORE Command along with WITH MOVE option

http://www.mssqltips.com/sqlservertutorial/122/retore-sql-server-database-to-different-filenames-and-locations/
Try out this solution
http://www.supportsages.com/blog/2010/02/mssql-two-common-issues-while-restoring-a-backup-and-its-solutions/
--
Chandu
Go to Top of Page

winny86
Starting Member

7 Posts

Posted - 2013-06-13 : 06:07:51
Hi bandi,

I have just realised through going through SSMS GUI that the computer in which this backup was originally made on had the path: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL1\MSSQL\DATA\DBName.mdf.

That is why I am getting the issue...and ur right, if I use the WITH MOVE then I overwite the directory in which the DB files are going to.

Thanks,

Dan
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 06:10:54
quote:
Originally posted by winny86

Hi bandi,

I have just realised through going through SSMS GUI that the computer in which this backup was originally made on had the path: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL1\MSSQL\DATA\DBName.mdf.

That is why I am getting the issue...and ur right, if I use the WITH MOVE then I overwite the directory in which the DB files are going to.

Thanks,

Dan


now got to work... right?
welcome

--
Chandu
Go to Top of Page
   

- Advertisement -