Hard drive failure, what should I do?

[eggy168]

The SQL Server 2005/Window Server 2003 server box has hard drive failure, so I ended up buying a new hard drive to replace. I have daily backup tape using the 3rd party backup application ~ Novastor in the old box with No Errors.

I restored the whole backup tape into the SQL Server 2005 folder using the Novabackup application. I seen all the databases in the hard drive, and I seen the databases under the Database Snapshots. However, when I tried to open it, there is nothing inside the folder, like Tables/view/and etc.

I read couple articles and some said I need to restore the system databases, Master, Model, MSDB and Tempdb, so I restored those into the hard drive as well. Unfortunately, I still don't see anything underneath the Database snapshots, but the user logins are inside the Security folders. If I have the SQL Server Management Studio opens for more than 5 minute, an error message comes up, "Cannot show requested dialog. (SqlMgmt); Method not found: 'Microsoft.SqlServer.Management.Smo.Edition; Microsoft.SqlServer.Managment.Smo.Information.get_EngineEdion()'.(SqlManagerUI)".

My question is should I uninstall the SQL Server and reinstall the application again or what should I do now? I still need to restore the backup tape data into the new hard drive. Can anyone expert(s) help?

Thank You.

 

[mkrausnick]

Well, I'm no expert, but I think you will need to restore each user database from the backup files created by your maintenance plan. The database files themselves are most likely not on the backup tape.

Unless you stop the SQL server each time you back up to tape, or your backup software is specifically designed to back up live databases, the database files are in use during your tape backup and therefore do not get backed up. Therefore you need disk backup files created by your maintenance plan.

If you didn't have an active maintenance plan prior to the disk failure, you will need to send the failed drive to a disaster recovery company to see if they can recover data from it.


Good luck!

Mike Krausnick
Dublin, California

 

[eggy168]

How about the back up tapes I have been using? You think the back up tapes are no good during the restore process?

 

[mkrausnick]

That depends on what you backed up. When SQL Server is running, the database files (.MDF and .LDF files) are open by SQL Server. Your backup software can't touch them. It will report that the files were bypassed because they were busy.

You have two options to back up your SQL Server data:
1) Implement a Maintenance Plan that causes SQL Server to back up your databases to disk, then back those files up to tape
2) Use backup software specifically designed to back up live SQL Server databases, such as CA ArcServe with the SqlAgent option.

In my installation, I use both. My SQL Server maintenance plan creates .BAK files, which then get backed up to tape, plus the SqlAgent option of CA ArcServe backs up the live database files too.

Of course, you needed this in place before your drive failed.

Do you have maintenance plan backups for your user databases?


Mike Krausnick
Dublin, California

 

[SQLBill]

Did you ever TEST the tape backups by doing a restore?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[eggy168]

Thanks for the replied. I think I have 80% data restored to a new hard drive.

The Novastor back up application doesn't have the SQL Agent feature like the ArcSrv. However, after I restored the tape, I only need to Attach the databases, then it is done. The only thing I still have trouble is the Login script, which I think it is dealing with the System database, either the Master or the Tempdb (If i am correctly). I had the problem before after restoring the master table. The SQL Server got corrupted or something. I am researching on how to restore the master table correctly.

I tried to use the SQL back up feature, however, when I tried to use the backup device, it doesn't recognize the device. I tried the sp_addumpdevice stored procedure, it still didn't recoginized the device. I need to do more research on it as well.

I had all the maintenance plan and transaction log back up into the hard drive before it crashed. I found it is not really useful if the hard drive dies again which I am not going to do such thing anymore unless I can back up in a different drive/or a back up device.

Any better solution or ideas?

Thank you.