SQL Server 2005 Express Edition Problems

Writing by on Monday, 29 of October , 2007 at 3:04 pm

Yes, I agree – Its painful. But remember, its Free. And the price to pay is that you have to do some some searching around to work around your problems. But yes, Microsoft should have been a little more relaxed and made life for us developers a little easier.

So my pain started with this error message.

Server: Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are allowed.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Turns out they had installed SQL Server 2005 and then SQL Server 2000 (earlier version) later and that caused the screw up. There is a way (I don’t know yet) where you can override which version to use, for compatibility purposes. This link will tell you that its a forward compatibility issue. In my case, SQL Server 2005 was used for backing up and restoring.

So I went ahead and removed both these versions and reinstalled SQL Server 2005 Express Edition and thats when the real PAIN started.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Remember, you have to configure SQL Server to allow remote connections. These posts show how.

But it STILL DID NOT WORK !!! Did some more searching and found the Problem.

The trick is in specifying the Server Instance correctly. By default it is SQLEXPRESS. So, if you were on the same machine your Server instance would be localhost/SQLEXPRESS. This post saved me

So there you go folks. Who would know SQLEXPRESS is the default instance name? Why don’t they just default it? Why don’t they just enable TCP/IP & named pipes for remote connections and save us the trouble? I mean I understand security concerns, but damn it there’s no data – Its an empty database. Once I have data I will disable whatever I don’t want.

Comments (1)

Category: Rants/Raves,SQL DB

1 Comment

Comment by Neal

Made Wednesday, 31 of October , 2007 at 4:05 pm

Thanks for writing this. I knew about enabling remote connections, but didn’t know about SQLEXPRESS instance.

Leave a comment

You must be logged in to post a comment.

Shivdev Kalambi's Blog

Shivdev Kalambi is a Software Development Manager, previously a Principal Software Engineer at ArcSight/HP. With over 16 years' experience in software development, he's worked on several technologies and played different roles and contributed to all phases of projects. Non-tech activies include Ping-pong, Rock Climbing and Yoga at PG, Golf, Skiing, Swimming & a beer enthusiast.