April 11, 2002 at 7:43 am
I had to restore a db from a tape device and redirected to a db I created for this purpose. I did this a few times for people in my company that needed to get some data from specific dates, please don't ask these are clients requests that never stop surprising me at all.
Anyway, as I was saying I did the restore and everythihg went ok, apparently. The database started to show as "loading" so, I waited for the process to complete. It never did. Still showing as "loading"
Any suggestion, what went wrong? Now I can get rid of this db. I stopped SQL server to see if that could help to get rid of that database, but it did not work. I did reboot the machine and didn't work either.
Any help is always appreciated
April 11, 2002 at 3:38 pm
Let me guess, Veritas restore? If the Restore utility is not native SQL and has the restore job showing completed, you should be able to detach and reattach the database and all will be well.
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
April 12, 2002 at 6:44 am
David,
Yes Veritas. It shows restore successful 100%. I tried to detach and reattach but it did not work. It came back with
"db cannot be detach while there is an active connection"
I am doing from the server itself. There is no other connection other than me, becasuse this server I used to restore is dedicated to those kind of cases.
Any other suggestion? My plan B is to take the db down on Sunday morning with no clients connected to the db, backup current db, restore that date on top of the production db, move all the data to an alternate db and then restore the currect db again, then take the db up again. Complicated? yes, but I don't see any other alternative at this point. Do you think this could work?
Thank you.
April 12, 2002 at 7:15 am
So when you do a sp_who2 is there any connections open to the database in question? If so, close that connection then do the detach / attach. If that still doesn't work then the stopping of all SQL Server services and starting again should work.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 12, 2002 at 7:21 am
Yes david, that's exactly what I did. I also used sp_helpdb to check that the db was in the the db catalog. It shows as a valid db. Veritas is sometime a pain in the neck. I did not know what you mentioned about the native db being restore from the tape using Veritas.
I think I will have to go to my "wonderful" plan B. If any other suggestion, please let me know I really appreciate your help.
April 12, 2002 at 7:26 am
My post wasn't too clear when it came to the native SQL backups. I prefer them over third party for a myriad of reasons, your problem being one of them. I always backup to disk and then use 3rd party to backup the SQL backup file. Works solid.
As for your plan B, you should be able to get away with stopping the mssqlserver service and starting again and your database will come back to normal. No other steps "should be required".
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
April 12, 2002 at 7:35 am
Good to know. It sounds like a plan. Do you use any script to get rid of the disk backups after they are bakedup to tape? I have quite a few dbs and space is one issue I need to check everyday.
April 12, 2002 at 9:28 am
Depends on the scenario. Some instances I will keep multiple days worth of files on disk other times I will overwrite each night. Some I will copy to other servers that have more space. I wish they were all the same but alot of what I have is inherited.
As for scripts, I would use a standard xp_cmdshell to clear out the file(s) before backup.
Hope this helps and if you want more specifics let me know.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 12, 2002 at 9:31 am
Thank you. I will see how can I get some space for this task. Thanks again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply