SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 lost disks, have backups, sql confused
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aberbotimue
Starting Member

26 Posts

Posted - 08/13/2010 :  15:28:56  Show Profile  Reply with Quote
Hi all..

I seem to have got in a pickle!

I have a sql server 2k DB.. it was running on a server with sql/os on disk one - the data files on disk 2 - backups on disk 3

I lost disk 2. SQL noticed, and has maked the DB's as suspect. and rightly so, the data files are missing..

I am wanting to recreate them onto disk 3, but It won't let me detach the existing DB's, etc.. and as such, the re attching is failing.

I even tried to create a new db, and restore into that, but that failed..

and now I am just flailing around, and not achieving anything..

what is the quickest waty to get the database back working..

I have backup files.
I have sql server 2 k with the dbs in there, but can't detach them.
I can alter the connection string for the software rto use a new DB name, but would need the backups to create thoses dbs for me.

any help would be welcomed... its the weekend, and i want to go home!

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/13/2010 :  15:35:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
Drop the existing databases, don't detach them.

What error did you get when you tried to restore them with a different name?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

aberbotimue
Starting Member

26 Posts

Posted - 08/13/2010 :  16:47:34  Show Profile  Reply with Quote
Thanks for the reply..

when i select delete I get this message.

http://twitgoo.com/1hv5ec

I will try the restore into another db agin, to get the meassage.. I just tried and i get another error, so will play, and repost when i get stuck again..

but even if I restore to a new DB, I will still need to get rid of the original DB's even tho their data is already awol.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/13/2010 :  17:18:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Check for the database here:

SELECT * FROM master.sys.databases

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

aberbotimue
Starting Member

26 Posts

Posted - 08/13/2010 :  18:50:18  Show Profile  Reply with Quote
I seem to have got the restore into a new db at least started, but considering the backup only ever takes 30 mins, the fact the restore has been going for over an hour now, i supect somthing is wrong..

as part of the restore process, it did mention somthing about the old path of the old DB, which no longer exists.. i am not sure how to tell if its restoring or not, or if its died...


I ran the

SELECT * FROM master.sys.databases

but its saying Invalid object.

I ran somthing earlier, can't reall what it was, and the databases were all listed. the three that were moved to have their data on the other disk ( the disk thats died ), that are marked at suspect, were not in the list. yet they do seem to be in enterprise manager.

I guess if this restore works, i'm not going to bne able to change the name to the old db, while it still exists in enterprise manager.
Go to Top of Page

aberbotimue
Starting Member

26 Posts

Posted - 08/13/2010 :  19:01:18  Show Profile  Reply with Quote
Although the restore is still going, it is working, and the disk space free is shrinking!!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/13/2010 :  21:44:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sorry forgot you were using SQL 2000. It would be master.dbo.sysdatabases.

If the path is changing, then you'd need to specify the WITH MOVE option which can also be done on the Options page in the restore GUI.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

aberbotimue
Starting Member

26 Posts

Posted - 08/14/2010 :  17:33:26  Show Profile  Reply with Quote
The restore I started has worked.. into another DB.. It just took a LONG time, but there are over 6 Million records in there, so I guess thats fair!

The DB is now running under a new name, and we altered the connection string in the App to point to the new DVB name.. new user, etc..

which only leaves changing its name at sompoint to the old name, as thats what we all know and love!!

The

select * from master.dbo.sysdatabases

does show the list not show the databases in the table, but they still exist in the local version of enterprise manager..

so i guess the next step is kill them off..
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/14/2010 :  22:43:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Have you refreshed Enterprise Manager? If it doesn't show in sysdatabases, then it is no longer there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

aberbotimue
Starting Member

26 Posts

Posted - 08/19/2010 :  09:35:39  Show Profile  Reply with Quote
after a reboot of the server they stopped showing. Thanks for that..

I assume its because I did try the delete, althouth it failed, it did remove the entry from the sysdatabases table.

The full text index that died with the disk is a harder one to get rid of.. I have deleted it, and it failed, but also after rebooting, its still in the EM interface.. any words of wisdom, how to get rid of the broken full text catalog//
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/19/2010 :  12:45:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't have any experience with that. You may want to start a new topic for that part so that we can get fresh eyes on your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000