Newbie (Know-nothing-bie:) Needs Urgent Assistance with SQLExpress

  • I have very little experience with SQL other than using it. I am a hardware tech. But I do have some knowledge of when something isn't working right and not due to hardware.

    One of my oldest clients had a problem with a dead hard drive. Unfortunately, replacing the drive and reinstalling the OS was the least of their problems.

    They have a business that has gone through several downsizings and currently there are only 2 people who need to access a piece of software they use to schedule appointments. This is a very OLD piece of software they have used for years. The company who originally sold the program is also gone, none of the tech support or sales phone numbers connect. They must have sold the "name" of the software as I can find the place that now sells it but they offer no support for it in it's original form.

    The last time I was involved with it was about 4 years ago when they had to replace their Windows XP system which was used as a "peer to peer" server for the data files.

    I remember talking to a tech at the company on the phone who guided me through getting the MDF/LDF's off of XP and onto Windows 7. I also remember it was not easy but once done it worked as before.

    The current problem was due to a hard drive failure on the 4 year old Windows 7 system. They have many years' of backup files but it is only the "data" in a ".bak" format.

    They still have an installation program to create the client end. But I remember four years ago that creating the SQL instance was a "do it yourself" thing. I still have the original password used at the time but it is of little use. Every attempt I have made to link the database using the supplied "DB connect utility" fails.

    I am not sure if the problem is something I am doing or if I need to be using a different version of SQLEXPRESS. Or maybe some small thing I have missed.

    I have tried various versions of SQL 2008 (and even 2005 ). I thought about trying 2012 but after spending so much time getting nowhere, I think I need some advice about what I am doing wrong

    I anyone here can point me the right way I would appreciate it. Better yet, I wouldn't mind paying someone to provide remote support for the SQL installation. Without this program they are back to pen and paper and the quotes for a replacement program are not in their budget.

  • Boy, you are not in an enviable Position!

    In your case, Trial and error is going to be one of your best bets as bad as that advice might Sound. Try starting with a late Version of SQL Server 2008R2 and work backwards restoring the database until it is successful. I would not recommend using 2012 at this time because, if my experience is anything to go by, it is not supported by many vendors at all.

    There is obviously a great deal more involved than just that (creating the instance, a new database, checking Network Settings and attaching the Client to the database) and if you find yourself struggling too much consider getting professional help.

    I will Keep looking at this post just on the off-Chance I can help one step further....

  • If this is something that the company uses to keep their business up and running, you cannot use an off the shelf, home user edition of the OS type of computer. You need a server that can withstand some great lengths of time with the proper maintenance. I have servers that are still running that I purchased used in the 1990's. But I maintain them well.

    You have not told anything about the error that you are receiving. You can always go forward in your version of SQL Server. But never backwards. Meaning, if your backup is of 2005, then you can restore that on a 2008 version. There is not need for you to be using SQL 2012. You are actually not using any of the latest aspects of 2012. You will not have a log in the backup file. You would have had to setup Log backups.

    Sounds like you need to create a database of the the same name on that instance. And then restore your latest full backup and allow it to over write. You need to create the same login with the same credentials. What are the credentials that the front-end uses?

    This is only a guess, since you did not tell us the actual error message you are receiving.

    Andrew SQLDBA

  • I'll throw my 2 cents in.

    Create an instance of SQL Server Express Edition in a version equal to or greater than the one used to take the backup. If it was on SQL Server 2005 EE, use that or above, maybe SQL Server 2008 EE.

    If you have a .bak file (recent one), you can do a restore it to an instance and it should restore just fine, as long as you have the same or higher version of SQL Server that was used to take the backup. Just make sure you save the data and log files that make up the database on appropriate disks and in appropriate directories. This can be set in the RESTORE screens of Management Studio, when you right click "Databases" and choose "Restore Database". Once you set the database name and choose the .bak file for the database, choose the Options page (on the left side of the form) and change where the actual data and log files will be saved. If you aren't that familiar with how backups work, SQL Server jumbles all the data and log files into the .bak. When you restore that file, it reconstitutes the files back into individual files, and you can change where it puts them, and their names for that matter.

    The other issue is that the restore will restore the database with all the permissions, but the login (if it is SQL Server Authentication, as opposed to Windows Authentication for using the database) is assigned a specific SID (Security ID). This SID is specific to this user for this database, and the login associated with it must have the same login. If the SIDs match up, the permissions will be exactly as they were on the old server.

    Once you get the old database restored, try this to see if it will trick it into using the same sid for the user. First, get the SID for the desired user from a listing of the database principals from inside the database. Then, once it gives you the SID for that user, replace that SID value in the CREATE LOGIN statement and give it a password, and it should (SHOULD) create a new login with the same sid so that the permissions will work from the database.

    Here are the statements to do this:

    --Get the users of the database, and whether they are SQL_USER or WINDOWS_USER, and their SID

    SELECT * FROM <databasename>.sys.database_principals

    -- then use the SID from the above statement results for the proper user and change the stuff with the <> brackets to create an instance user with the same sid.

    CREATE LOGIN [<username>] WITH PASSWORD = '<somepassword>', SID = <sid from the select above>, DEFAULT_DATABASE = [<new database name>], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

    Hopefully, this will create the desired login, with the appropriate sid to keep the permissions in the database. This scheme, as it is, will only work for SQL Server authenticated users. If Windows users and it is on the same domain, just create a Windows Authenticated login for that user and it will use the same sid as the Windows user in the domain.

    You will need to use the same password, if known, that the program is using. Since the old server is not available, you can't script out the logins with the proper sid and password hash (which is a weekly DR procedure for me for all my instances that I am responsible for).

    Hopefully, this will give you the same permissions for the given user (I have not tested, but SHOULD work). Definitely worth a shot.

    Hope it helps. Please let us know.

  • I have just thought of a couple of things that may help...

    You can group your users into groups, either via AD (if you have it) or using Local Users and Groups if the server is in a workgroup. It may be easier to put the required users into a single group and grant that group access to the database. Where different levels of access are required, create and assign differing groups accordingly. If the SQL Server users were created from Windows logins, the issue with SIDs becomes moot, saving alot of trouble. If not, then I would simply delete/remove the accounts from the database and recreate them.

    The point here is that the SID in itself is not necessary, it is the user that counts. So instead of finding the SID of a user and recreating it, simply remove the user and then create a new login and the associated mappings.

    As long as you can restore the database without errors, this may be your fastest route back to it being usable.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply