SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Restore to directory does not exist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

winny86
Starting Member

7 Posts

Posted - 06/13/2013 :  05:02:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/13/2013 :  05:18:33  Show Profile  Reply with Quote
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 - 06/13/2013 :  05:20:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/13/2013 :  05:27:06  Show Profile  Reply with Quote
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

Edited by - bandi on 06/13/2013 05:34:11
Go to Top of Page

winny86
Starting Member

7 Posts

Posted - 06/13/2013 :  05:41:46  Show Profile  Reply with Quote
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 - 06/13/2013 :  05:50:35  Show Profile  Reply with Quote
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 - 06/13/2013 :  06:00:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/13/2013 :  06:04:22  Show Profile  Reply with Quote
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

Edited by - bandi on 06/13/2013 06:10:04
Go to Top of Page

winny86
Starting Member

7 Posts

Posted - 06/13/2013 :  06:07:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/13/2013 :  06:10:54  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000