June 6, 2002 at 3:55 am
I have thumbed through issues posted in this forum, but haven't found one in particular that answers my problem.
Following is an email to Andy Warren with reply:
-----Original Message-----
From: S.F. Mjølsnes [mailto:mjolsnes@c2i.net]
Sent: Tuesday, June 04, 2002 10:11 AM
Subject: Comments on Swynk Article: Moving SQL to a New Server
Dear Andy,
Judging from your article, you must be the right person for my database moving problem.
I am a computer scientist recently stumbled into to practical world of Mr. Gates.
I took a course in 1979 on databases and their normalization hierarchy ;-), but this
time it is about practice, not theory. Now for the practical challenge:
A server rack of three servers does not function anymore, partly because of
broken powersupply, and lack of complete knowledge about the links between them:
a main domain controller (I believe), then a webserver, behind a SQL 7 server with a DB.
This is to be moved to new computers, so I have managed to copy out most of the stuff.
In my naivite I thought the database was safe by just moving the data.mdf and datalog.ldf,
the two database application files to the correct place in the a new home with Microsoft SQL 7.
Not so, I can assure you, but you already knew...
So I am probably not able (not without a lot of effort) to the "detaching" stuff in the old home,
because the SQLserver will not start on the old machine.
Am I in a "check mate" situation, or would there be a direction of this mess.
The SQL server complains that it is not able to attach because some file activity is lacking,
or something.
Are there inspection utilities that understand the format for those .mdf files so I could dump out
the tables?
I have access to SQLServer2000, but will that help?
Although I only skimmed your articles till now, I appreciate if you could point me toward
the right direction in this.
Regards,
Stig Mjølsnes
Norway
Fra: "Andy Warren" <awarren@cfl.rr.com>
Dato: 2002/06/04 Tue PM 11:01:41 CEST
Til: "S.F. Mjølsnes" <mjolsnes@c2i.net>
Emne: RE: Comments on Swynk Article: Moving SQL to a New Server
Normally you can attach files without a problem. Usually the only reasons for an error is if the file has been damaged (rare) or there was more than one log file and one is now missing (can be fixed).
Im a co-owner of http://www.sqlservercentral.com (free registration required). If you'll post your questions and any error messages in our discussion forums, you'll usually get an answer or two within a few hours. Well worth the time I think.
Andy
The particular error message I receive is:
"Test connection failed because of an error in initializing provider. Database "db4" cannot be opened because some of the files could not be activated."
So what can be done?
Regards, Stig.
June 6, 2002 at 5:34 am
Usually this means you are missing a file that SQL expects to be able to reattach the files. This may be the the primary file, a secoundary file, or the log file. To get the answer open SQL Server Enterprise Manager and drill down thru the server/management/SQL Server Logs and look in them, should give you an answer. Also when attaching the database you have to specify the location of all the files if they are not the same path as previously on the other machine. See sp_attach_db in SQL Books Online. If the file is a single file and you don't have the log then also look at sp_attach_single_file_db in BOL which unless you had a multi-part log file attach the DB and generate a new log file.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 6, 2002 at 5:48 am
You may find this article (and the links it contains) helpful:
http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp
Andy
June 13, 2002 at 2:46 pm
Well, I've come a long way in the practice of SQLServer. The stored procedure call of sp_attach_db worked like a snap for my files,
as suggested. In the mean time I have been boggling with some encrypted stored procedures, but that is another story.
I probably still have some miles to go because of the various login ids and user names and associated rights, because as I understand from one of your articles, the
user authorizations do not come automatic in the sp_attach_db procedure?
Hints on the user authorization problem are
most welcome.
Regards, Stig.
June 13, 2002 at 4:21 pm
Logins are stored in master with a SID. Users are stored in teh db and map back to a login. If you restore the db, the logins are not created due to possible conflicts.
If you can create the logins, sp_change_users_login will synch them up.
Steve Jones
June 13, 2002 at 6:45 pm
It will link the SPIDs in SQL based on the user name matching. It will not repair passwords, those have to be done by hand or possible pulling the password data from the sysxlogins or syslogins table (sorry no server right here) in the original master.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply