March 31, 2008 at 12:47 pm
Hello, I need some help ASAP. Our server crashed a week ago and we need to get a database back up. Problem is we ran our old database on SQL 2000 and now our OS is SBS 2003 which they don't recommend installing SQL 2000 back up so telling us to upgrade to SQL 2005. I get the DB to load through the restore, but I don't get access from my front-end client due to the whole login error problem. But All answers I've seen are when you have a db up to get info from which I don't have since it totally crashed. All I have is the .bak file. How can I get this to connect with my front end client? I've searched and found posts but they are a bit confusing, and when I try them it isn't working, so I must be missing something.
March 31, 2008 at 1:02 pm
Restoring an SQL 2000 database on SQL 2005 is usually just a simple matter of restoring from the backup. I just did five of those last Friday (getting ready for a major migration project).
But I'm not understanding what you wrote about problems with your front end application.
Can you clarify: Can you restore the database on the 2005 server, and can you then access data using Management Studio? If not, what errors are you getting?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2008 at 1:03 pm
have you got a backup of the old master from 2000? Restore it into the 2005 instance as a database of a different name (ie master_2000) as if it was a normal database. You can then use sp_help_revlogin (the version that goes from 2000 - 2005) againt syslogins table in this database to script out the old logins.
I've not actually tried this in this exact scenario but got to be worth a go.
---------------------------------------------------------------------
March 31, 2008 at 1:13 pm
I'm getting the error:
[Microsoft][SQL native client][SQL server]Login failed for user 'LIMSUSA', LIMSPROD
when I try to connect with my front end client.
I can connect through SQL management studio fine.
March 31, 2008 at 1:14 pm
All the instructions that I have read for sp_help_revlogin show that you need to do that PRIOR to moving a database, that is in your existing database, that is where my problems lies, in that I no longer have a running database. I guess I may be incorrect in reading that though.
Sorry I think I just understood the question. Do you mean a copy of the master.db from 2000? If that is the case, no just a .bak file of my actual database was kept.
March 31, 2008 at 1:14 pm
Hello,
It sounds like you had an 2000 db that all you have is a .bak file of now correct? Now you have a 2005 server to restore to?
If so - Create a new db on the 2005 server in management Studio (same name or different). Also on the 'Compatibility' option set it to SQL 2000. After this is created right click on the database and go to Tasks->Restore->Database. Choose the device/file path, double check that the correct .mdf, .ldf file paths are right under Options and restore away.
Hope that helps.
GP
March 31, 2008 at 1:20 pm
For your login issue please try the following - Delete your user at the Database level. Create the user at the Server Level and give your self database permissions under the User Mapping section. If you restore the database the db level permissions will be there but your login will not be there at the server level which can cause issues in certain apps.
Also just double check under the SQL Server Surface Area Config tool that the server is set to allow remote connection using named pipes or TCP + Named pipes. (I don't think that is the issue but worth a check anyways)
Thanks
GP
March 31, 2008 at 1:25 pm
asizemore (3/31/2008)
I'm getting the error:[Microsoft][SQL native client][SQL server]Login failed for user 'LIMSUSA', LIMSPROD
when I try to connect with my front end client.
I can connect through SQL management studio fine.
are you connecting to SSMS with the same ID as when you try and connect from the App?
Have you installed the 2005 instance to use mixed mode authorisation?
Does the login above exist in the 2005 instance?
---------------------------------------------------------------------
March 31, 2008 at 1:30 pm
What is the version of Sql Server . For Sql Server Express and developer edition by default id allows only local connections.
Go to Surface Aread configuraion and change the settings for allow remote connections.
is your front end and db are on same machine???
NJ
March 31, 2008 at 1:41 pm
OK I removed SQL 2005 and installed again to start fresh. I tried adding a new db. then restoring to the db as suggusted above, but I get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'PRIME'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'LIMSPROD' database. (Microsoft.SqlServer.Smo)
In past I restored the database directly (i.e. I didn't create a new db, then restore).
March 31, 2008 at 1:44 pm
[highlight=#ffff11]are you connecting to SSMS with the same ID as when you try and connect from the App?
Have you installed the 2005 instance to use mixed mode authorisation?
Does the login above exist in the 2005 instance?[/highlight]
I am a bit new t SQL only been using it for 3 years, most was self-taught until went to some classes at Learning Tree International, which is just basics. So I don't know what you mean by SSMS, sorry. The front end application appears to be looking for a password automatically when logging in for LIMSUSA. It never gives me a chance to enter one. And I have no clue what it use to be.
March 31, 2008 at 2:10 pm
SSMS isSQL Server Management Studio
SO, you lost a SQL 2000 instance and have replaced it with a 2005 instance. You have a backup of your app database but not the system databases.
If so, delete the database you just created via SSMS. Restore your APP database the way you originally did it, this will create your database for you and upgrade it.
You have no way to restore the login as you do not have a backup of the master database (are you sure?). So, create the login again from scratch via SSMS and assign it a (secure) password. If the app does not prompt you for a password it must hold it somewhere (a .ini file?). updata the password to match the one in SQL. Hopefully some one knows the app and knows where it is held, and hopefully its not all over the place.
You then need to tie the login to the user in the database. Switch context to the app database and run
sp_change_users_login 'update_one','LIMSUSA','LIMSUSA'
If you cannot update the password in the app to match the one in SQL then I am afraid we are a bit stuck.
It is still also worth checking all the other options that have been suggested.
If you had any jobs in SQL Agent or DTS packages they are also lost without system DB backups or other backups of these items. A lesson for next time!
---------------------------------------------------------------------
March 31, 2008 at 2:23 pm
Hello,
It should work. When you created the new db that created new .mdf and .ldf files. When you tried to restore the new db did you go into options and ensure that the restore process was pointed to the new files (By default it will try to find the files that were previously associated with old database). Also check the 'Overwrite existing database' checkbox.
Finally did you make sure the compatibility level is set to SQL 2000? I'm not 100% sure that is necessary but think it is.
Thanks,
GP
March 31, 2008 at 2:28 pm
I'm sorry george sibbald, I think I may HAVE a .bak of the master file on my backup tape the night before the server crashed. What do I need to do with that file?
March 31, 2008 at 2:49 pm
asizemore (3/31/2008)
I'm sorry george sibbald, I think I may HAVE a .bak of the master file on my backup tape the night before the server crashed. What do I need to do with that file?
restore this backup of the sql 2000 master database into your 2005 instance as an ordinary database:
restore database master_2000 from disk = 'your master .bak file'
with move 'master logical data file name' to 'new physical location',
move 'master logical log file name' to 'new physical location'
Copy the method 2 sp_help_revlogin script from http://support.microsoft.com/kb/246133/
Run this into master_2000 to create stored procs
then 'exec sp_help_rev_login' .whilst in master_2000
the output of that will be a script you can run into SQL 2005 to recreate all your old logins with the SAME password as before and the SAME SID. edit out any logins you don't need.
Using this method your login should tie up with the user in the app database and the application should then be able to login.
I have used this method in other scenarios but not actually when the database will be upgraded on the way in, but I think it will work so give it a go. If it fails because of the upgrade bit , restore your master backup into a SQL 2000 instance as master_2000 to reproduce login script.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply