Can I restore a full sql mdf to a sql express server?

[timfoster]

I have just moved my web hosting from a shared server to a vps. The database on the shared host was full sql. On my vps it's sql express.

Should I be able to attach the mdf file in sql express as normal or are there some full sql specifics in the file? I didn't think there were, but I get a problem when I try to restore it.

 

[timfoster]

Sorry, I just reread my post. When I said restore, I didn't mean restore I meant attach. Both times! Soz!

 

[gmmastros]

I get a problem when I try to restore it.

Error messages? Care to share?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[timfoster]

The error is "CREATE FILE encountered operating system error 5 (Access is denied) while attempting to open or create the physical file

 

[gmmastros]

Sounds like a permissions issue.

Make sure the sql server service is started with an account that has full permission to the folder that the physical files are in.

There seems to be a lot of good advice here:

http://social.msdn.microsoft.com/fo.../thread/11546d68-7eaa-4965-8bc9-09a92f8e1f6f/

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Arawn]

Do you have authority to the folder where the .mdb file is located?

Is it on the same computer as SQL Express? (I have never been able to get SQL express to use network drives)

SQL Express can attach files but it does have a size limit.

 

[timfoster]

I have permission on the box. It's a VPS so I have full admin rights over the server. I'm logged in as Admin with full rights. The mdf file is in a folder on the C drive which is where sql is installed.

I should definitely be able to connect a full sql mdf to a sql express installation shouldn't I?

 

[jbenson001]

Is the mdf or ldf files flagged as read only in windows?

 

[gmmastros]

I should definitely be able to connect a full sql mdf to a sql express installation shouldn't I?

Yes.

Click Start -> Run
Type services.msc
Scroll to SQL Server
Right click -> Properties
click Log On tab

Does the logon account have permissions to the folder?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[timfoster]

OK. Thanks for all the advice folks.

I moved the file to the sql folder with the other dbs to see if it was folder related. It wasn't.

So I set the permissions so that everyone had full control over the mdf. I got a bit further, but still not connected.

Finally I set everyone with full control on the log file and bob's your uncle, we're up and running!

Not sure what account needs full permissions. Is it just the sql service account, or the web service too? Either way I'll figure that out later with a dev version of the db.

Thanks again for all your suggestions. Now, can anyone solve my email issue over in the asp forum?