[Guide] How to configure your SQL database in SQL2005 Express

Backo

New Member
Joined
Jan 17, 2009
Messages
1,212
Reaction score
975
Installing SQL 2005 Express

Nothing special needed here apart from one thing. During install SQL Express defaults to a "named instance" and you DO NOT WANT THIS. Make sure you set it to a "default instance". If you have installed already and are not sure check the service in your services control manager, it should say "SQL Server (MSSQLSERVER)" if correct and if it has "EXPRESS" in there anywhere then you have a named instance. Uninstall and do it again if you see "EXPRESS" in there.

Add the databases
Ok, lots of people say copy mdf file to blah or use the "attach database" function with the mdf file and this is not right. The mdf file that could well have been messed with and not the proper backup.

Right-click Backup devices and select New backup device. Give the device a name and select the proper backup, the one without and extension (a .??? in it's name). The one below is the account database but there should be one for the gamedb and cabalcash. I can see no reason why the others are needed as i have not seen any evidence of them being actually used (please correct me if i am wrong there, but i don't use the others and don't have problems).



Now do the same for the cabalcash and gamedb (give them their own names though!).

Next create the database to restore to. Right-click Databases and click New Database. Call it "ACCOUNT" (or Gamedb or CabalCash, you need all 3) and click Ok.

Right-click the database and follow the menu into Tasks->Restore->Database.



Set it up like shown above (remember the different names) and click the button with the 3 dots on the right opposite "From device".


Select "Backup device" and NOT "File" from the backup media dropdown, click Add and select the backup device you attached earlier.

This looks promising if you look at the data at the bottom :D


Tick the tickbox shown and then click Options.


Make SURE you tick that tickbox or the restore will FAIL.

Do that for all 3 (ACCOUNT, Gamedb and CabalCash) all using the files with no . in the name and you have the proper databases and not some dump done by somebody else!

Configuring the server to accept remote connections

Now run the "SQL Server Surface Area Configuration" tool from the MSSQL folder inthe start menu and check it allows remote connections as shown below...

If you make any changes above you need to restart the service. You can do this on the panel above remote connections on the left of the panel.

Configuring permissions for your cabal userid

Now check your cabal userid is set up like this (the password policy option, default database and language don't matter)...

(if you have not set up a user yet when you right-click "Logins" and select New instead of right-clicking the "cabal" user and clicking Propertries. You will get to the same screen).

If it does then make sure the user has ALL of these tickboxes ticked to give it full access to the database...


If the cabal userid doesn't appear on the panel above then add it like this...


I'll add more later when i get time (and when i work out how to do the stupid linked server in express :/)
 
  • Like
Reactions: Nicky
Thread starter Similar threads Forum Replies Date
Backo Guides 2