July 10, 2002 at 2:36 pm
I ran a typical backup of my database. When backing up,I had it backup to different .ldf and .mdf files. Thinking this would ensure the safety of my original files. After the backup, all seemed to go well. However now I appear to be locked out of my database. None of my logins work. One is the domain and the other is the SA. No luck on either. I renamed the files to their original names. This still didn't fix the problem.
Ideas?!?!
Thanks as usual!
July 10, 2002 at 3:35 pm
Not sure what happened. Usually backups go to xxx.bak file, totally separate from the mdf/ldf. Did you do a sql backup? Or stop the service and make copies of the files??
Andy
July 10, 2002 at 3:37 pm
I have been running SQL Backups with SQL Server. I did a recover. I didn't know it would add the .bak. When you look at the option to change the name, I changed the name to "*restore.ldf" and "*restore.mdf". When looking at the Event Log, it is saying that it can't open the two new files.
Thanks
July 10, 2002 at 4:38 pm
Bak is a convention, not enforced. So the backup was successful but you had problems restoring? Did you do this from Enterprise Manager? A list of steps taken so far would help.
Andy
July 10, 2002 at 5:44 pm
Andy,
I found out a table was deleted. As a result, I chose restore database. I didn't want to override my original database. My original goal was just to take the table and import into my current database.
I don't have the dialog in front of me (since I can't login). So I will have to do this by memory.
I had it restore a complete backup from a tape. When it gives you the option to modify the name of the file, I appended a "restore" to both the log and the data files. I expected to then have four files (two from my original database and two from the restored database). I also told SQL to go into READ-ONLY mode. After the restore, I didn't see any additional databases. Instead I had major security problems. I existed out of EM, went into my data folder for SQL and there I only found the two files (both renamed to *restore. The original files were gone. Somehow it overwrote the original files. Now, when I go to EM, it won't allow me to login with any user. I have tried my SA account, two domain level administrator accounts, and a few logins.
Nothing has worked to this point.
Hopefully you can help.
Thanks again for you efforts!
July 10, 2002 at 6:19 pm
Interesting. Did you select the force restore over existing db option? Thats the only thing I've thought of that would cause your previous db to disappear. Regardless of what happened with the restore I'm short of ideas on why your logins would no longer work. Logins/passwords are stored in master and cannot be restored without some extra effort compared to an ordinary restore. Have you tried connecting using Query Analyzer? Sure you're using the right login/password, trying to connect to the right instance if more than one, right server? Doesnt hurt to dbl check those kinds of things! What about looking in the event log/sql log - should be able to get some information about what happened there. Do you have a backup of master?
Andy
July 10, 2002 at 6:27 pm
Yes, I am sure I am using the correct login. When I did the restore, I went to the last full backup and restored on that date. I shouldn't have restored the master, etc. I don't think. How would I know? I just asked about those two files. No, I didn't ask it to overwrite. Yes, I have a backup of master. I run a full backup and database maintenance every Sunday morning. Interesting - I hope there is not a problem with my backups. I am questioning because I can see them if I go into NT Backup. However I tried to restore from NT backup but got back files with 0 bytes. Do you know if that should work the same way? I tried connecting through Query Analyzer ... no luck. I am getting the following error:
Unable to connect to server
Server msg 4064 level 16 State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
Any other ideas?
I just installed a fresh copy of SQL Server 2000 on another computer. I am copying over the .mdf and .ldf as we speak. I was hoping I just might be able to attach. What are your thoughts? Am I going to need to reestablish the master database as well? Should I be able to just copy those over and attach as well? What do you think about this method?
Thanks!
July 10, 2002 at 8:21 pm
Not being able to open the default db makes sense, since it is probably the one missing. I always leave all of mine set to master just for simplicity, never experimented with dropping a db referenced as a default. You get this even with SA? Would the sa have a default db of other than master?
Zero bytes is bad. I'd definitely dbl check what is going on there. You're backing up directly to tape?
You can't attach master. What you can do is stop the service, rename master mdf/ldf, copy the new ones into the same folder, restart the service. Then you'd have to attach any other db's, set up the all the logins again.
Andy
July 10, 2002 at 8:38 pm
I am really just a novice with adminstering SQL Server. As a result, I am not too sure how to answer question about the default. My default database is actually named "Geomedia". I have renamed the SA account to geomedia admin. It has worked on that account in the past.
I coped over the two files the the new SQL Server and then attached. All seems to be well and that worked. By new concern is that that was a backup dataset from mid June. Here is my current situation:
SQL Server 2000 running fine on one server with datasets from June.
SQL Server 2000 not running on my original server with the same datasets.
My thought would be to try and recover the July backups to the new server. Then rebuild the old server, use the copy database wizard, and move all back. What do you recommend I do about the other databases? Should I just copy the .mdf and .ldfs over the the working server?
I would rather not rebuild the bad server. Do you know of another way to reestablish a new default database so I can just import the copy and move on with life. As of right now, I can't do anything with the bad server. I'm sure I would then be able to reattach the other databases and all SHOULD be fine.
Thanks again!
July 11, 2002 at 2:48 pm
Try the following command from a command prompt:
isql -Usa -P<sa password> -S<server name>
According to the note in the Microsoft KB because isql uses DB-Library you will be able to connect and you'll be in the master database.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply