All Restores Failing
-
I have to create a duplicate of our production server to test a software upgrade. I have never used SQL- don't know scripting etc. But here's the problem:
SQL 7, NT 4 box. Built with same logical drive configuration, etc. Magic Enterprise is the application using SQL.
After the clean install of SQL and Magic all connections to the databases are fine. Full backups of the production DBs are done with EM and I copied them to the local drive of the new machine. I start SQL in single user mode, start the restore of Master from EM. It says it's writing file 1, then fails with SQL state 01000 and my master is destroyed.
As I said - I am a complete beginner here - any help would be greatly appreciated.....
-
Try this, I have had some issues getting it to work but, shut down SQL server on the original bo and the new box and copy replacing all files, the databases from the original server to the new server in the place expected. Then restart the new box. When done it should start and act like a server that has been renamed, and should if you copy all the databases over already be up like the other was.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
I've made some progress. I got the master restored using Query analyzer. I then did a system table update as follows:
USE master
go
UPDATE sysdatabases SET filename='d:\mssqlt\data\tempdb.mdf' WHERE name='tempdb'
go
ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'd:\mssql7\data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'd:\mssql7\data\templog.ldf')
go
I then detached and reattached the model, and MDSB databases.
Now Master,Model,Tempdb, & msdb are fine and SQL is running.
New problem: Northwnd, pubs and the MTSDE databases are suspect. When I try to detach them it says they don't exist. I've verified names and paths. When I use sp_help db it tells me No permission to access Pubs and Northwind. MTSDE is the database used by Magic. I can delete and recreate it but can't as it is marked no access. I probably don't even need Pubs and Northwind for the app to work.
How can I make the Master see these databases again?
-
Definite need the attach to work. Are you doing it from QA or EM? If QA could you post the syntax? Does your db have more than one log file?
Andy
-
I'm using QA and have tried it 2 ways
1. Started SQL normally and used this
Use master
go
sp_detach_db 'MTSDE' (or 'pubs' or 'Northwnd' - all of them fail the same way)
go
2. Start sql with sqlservr -c -g -T3608 and use
exec sp_detach_db N'MTSDE'
go
Both ways tell me the database doesn't exist.
The data directory the files as northwnd.mdf, northwnd.ldf, pubs.mdf, pubs_log.ldf, MTSDE_Data.MDF, MTSDE_Log.LDF. The MTSDE doesn't look right to me but I don't remember if it was just MTSDE.mdf before all this.
-
I would try a restore of the MTSDE database from a backup off the other server. Sounds like master is not referenced right to that db and the only way that I can think of getting it back is to do the restore.
Others may have better ideas.
As for the names of the physical files, they look like the standard naming conventions.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Ok since we have a backup (we do have abackup of the files right
) Try using sp_resetstatus DBNAME.Note see BOL for more information, here is just a snip of detail and you may not need all the steps.
Resetting the Suspect Status
Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:
Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
Free disk space and rerun recovery.
sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.
Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Ok - thanks for all your help. I now have all my databases back and running and have been able to recreate the MTSDE database for the Magic Enterprise database. Now a new problem:
I have created a new default Magic database(MTSDE). I have a backup of the live database. The live database has been modified and holds different data than the default on the target server. When I try to restore it I get:
SQL-DMO(ODBC SQLState: 42000)
The backup set holds a backup of a database other than the existing MTSDE database. Backup or restore terminating abnormally.
Is there any way to get this restored?
-
A little more info:
The first backup I tried by telling it to backup from device and pointed to the backup. I tried changing that to point to a file on disk and got a different error:
The volume on device 'd:\mssql7\backup\MTSDE' is not part of a multifamily RAID media set. Use with Format to form a new RAID set.
We did not have a spare server with a RAID array to build as the test machine I am trying to create. The new test machine is built on a workstation. Can anything be done to make this work?
-
Can you tell me a bit about the file structure of the production instance. Do you have multiple files for the filegroup(s) and or the Log File? I assume based on yuor message but want to be sure.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Keep in mind I'm a total novice here....I assume you're talking about the files structure in the data directory? If so then I only have the MTSDE.mdf and MTSDE_log.ldf files.
I tried forcing the restore. It worked but I still had errors accessing. What I tried next is uninistalling Magic - the app that uses MTSDE. I reinstalled Magic but did not create the database in SQL or let magic try to create and populate it. I deleted the database in EM and did a restore with no database created - let the restore create it. The database seems fine until you go to properties and try to access permissions - it locks up EM and you have to end task.
From some of the errors it looks like the APP (Magic) tries to index the database on 1st login. It fails and we get VB script errors. We had also gotten errors with references to NAMmetadata.2 and metadata.cpp. I reinstalled SQL and included full index. Now we only get the VB script errors.
It also fails trying to import metadata from this database.
It seems to be a problem with permissions on the MTSDE database but I can't access to check them?
-
Have you fixed the user logins? sp_change_users_login 'report' will give you an idea of which logins aren't working. More in BOL.
-
Thanks for everyones help. After much frustration I ran the client network utility. It had only TCP/IP network library . After adding multiprotocol and named pipes with the same parameters as in the server network utility everything worked. Also - had to edit the server utility as it had local host instead of the computer name.
Thanks again for everyones help - for someone who never worked with SQL before this was a real learning experience
-
Great to hear you got to the root. Hope you took good notes, as these things always seem to come back into your life when you least expect.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
I see that you have resolved.
Just a note, I have several customers that run into this quite a bit.
I have them restore the master from TSQL and quit using the EM
Then, if the restart fails, have them start SQL with the /f. This rebuilds the MTSDE
Food for thought.
) Try using sp_resetstatus DBNAME.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply