• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 720
  • Last Modified:

database restore problems in sql express 2005 using sql express management 2005

Thought you guys might have some insight on a restore problem I have run into in regards to the live database populated by dotnetnuke and trying to restore it on a local computer running sql express.

I reformatted my computer and than installed the .net framework up to 3.0 along with sql express and sql management express 2005.  When I try to restore the database now I get the following error:

-------------------------------------------------------------------------------------------------------------
restore failed for server cyboplus\dotnetnuke (microsoft.sqlserver.express.smo)
system.Data.SQlClient.sqlError: directory look up for the file e:\sqldata\Databasename_DATA.mdf failed with the operating system error 2 (the system cannot find the file specified).(Microsoft.sqlServer.Express.smo)
--------------------------------------------------------------------------------------------

I recieve this error when I go to my sql managment express 2005 right click on database than left click on restore database type in the database file on the top and click on restore from device find the .bak file name within the c:\program Files\microsoft server\MSsql.1\mssql\backup\databasename.bak\

I know that it is getting this path from the options tab within the restore window.  On the left hand side their is a general tab and an options tab.  However when I try to change the location of where it wants to restore the original database file as it came off of the host server I get an error because it wants me also to deal with the path of a file called TheDataBase_log theDatabase being a generic name for the actual database file. It wants to find that in another drive called f: probably also a drive where it is on the actual host server.

-------------------------------------------------------------------------------------------------------------------

My question in all of this is why is it looking to the path where the database file was backed up from to my server when I specifically specified that it ought to come from a device file located in the back up directory of my microsoft sql folder it is almost as for some reason it won't accept the fact that I want to back up the database from that location I specified but rather wants to somehow restore the original database file as it was called on the sql server of the host in the live environment.

I logged into sql as windows authentication when doing this.  I didn't run into this problem when I did restored the database before but only after formatting my computer and reinstalling windows xp pro with service pack 2 did I get these errrors.

By the way I looked and the sql services within in the admin tools services and found my instance of sql to be pointing to local machine not to that nt authentication\network services group.  That is the extent of troubleshooting I could think of and from there have just been going in circles trying to imagine what could have been different with sql server before formatting my computer and reinstaliling the database any way any help on this issue would be very appreciated thanks Lee or anyone else who might be reading this post.
0
cyborama
Asked:
cyborama
  • 3
  • 2
1 Solution
 
YveauCommented:
Try running a restore script, looks what that result is. I thin kyou are looking for something like this:

RESTORE DATABASE [dotnetnuke]
FROM  DISK = N'D:\Path\To\Backup\dotnetnuke.bak'
WITH  FILE = 1
,  MOVE N'dotnetnuke' TO N'D:\Path\To\Data\dotnetnuke_Data.mdf'
,  MOVE N'dotnetnuke_log' TO N'D:\Path\To\Data\dotnetnuke_Log.ldf'
GO

Hope this helps ...
0
 
nmcdermaidCommented:
>> why is it looking to the path where the database file was backed up from to my server when I specifically specified that it ought to come from a device file located in the back up directory of my microsoft sql folder

I don't know... its always worked that way but it seems a bit of a pain to me. Perhaps its a hangover from SQL 6.5.

0
 
YveauCommented:
... I think my suggestion would have solved it ?!
And I don't come that many questions on dotnetnuke in the SQL zones ... maybe it's of some interest to the knowledge base even though the Q is actually about restoring a database on another server.

Yveau
0
What Security Threats Are We Predicting for 2018?

Cryptocurrency, IoT botnets, MFA, and more! Hackers are already planning their next big attacks for 2018. Learn what you might face, and how to defend against it with our 2018 security predictions.

 
nmcdermaidCommented:
Yep I agree, give Yveau the points. Its a correct answer.
0
 
cyboramaAuthor Commented:
I got the resolution but it was totally different than what you gave but since I can see how that would be a solution as well I've decided to give you the full 500 points

0
 
YveauCommented:
Thanks nmcdermaid and LeeTutor.
... and cyborama for the grade !
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now