June 16, 2011 at 5:35 am
Hi folks,
This is some critical issue we are facing in our production environment.
We have a production instance which was installed on SAN-LUN.
Somehow that LUN got deleted, and now SQL service for that instance is not starting up giving following error:
"The SQL Server service terminated with service-specific error 17058 (0x42A2)"
Application logs show up an error that it could not open the sql server error log file.
We have a backup of databases of that instance but unfortunately do not have backups of master or any other system databases, jobs, logins, roles, permissions etc. for that instance.
Can anybody suggest me a solution?
This is very urgent and your help is appreciated.
June 16, 2011 at 5:39 am
viduvs (6/16/2011)
Hi folks,This is some critical issue we are facing in our production environment.
We have a production instance which was installed on SAN-LUN.
Somehow that LUN got deleted, and now SQL service for that instance is not starting up giving following error:
"The SQL Server service terminated with service-specific error 17058 (0x42A2)"
Application logs show up an error that it could not open the sql server error log file.
We have a backup of databases of that instance but unfortunately do not have backups of master or any other system databases, jobs, logins, roles, permissions etc. for that instance.
Can anybody suggest me a solution?
This is very urgent and your help is appreciated.
Find a new disk, reinstall SQL Server from scratch, add back all the linked servers, jobs, SSIS packages, logins, and role perms that you can remember, then restore your user dbs. If you have TFS or VSS or other source safe type storage, you might be able to recover packages and job code from that.
After which, you should start backing up your system dbs. Master, MSDB, and Model (though the first two are the most important of the three IMHO). TempDB doesn't need to be backed up as it sort of recreates itself every time SQL Server is restarted anyway.
EDIT: Does your company have external backup software that integrates with SQL Server? Maybe when backing up the SAN, they got your system DBs backed up properly too? If the software doesn't integrate with SQL Server, don't bother trying to restore the SAN. The data files will be corrupted and unusable.
June 16, 2011 at 6:34 am
Thanks for your reply.
I was having the same solution to install a new fresh instance and then configure the things.
Though we don't have a backup (even external backups), we have the things (jobs, logins etc.) documented.
Just wanted to know what if we can have different solution other than fresh instance configuration.
June 16, 2011 at 6:41 am
I wish I could tell you otherwise, but unless you've got some other availability set up (log shipping, Database Mirroring, etc.), you're stuck doing it the old fashioned way.
June 16, 2011 at 7:45 am
viduvs (6/16/2011)
Thanks for your reply.I was having the same solution to install a new fresh instance and then configure the things.
Though we don't have a backup (even external backups), we have the things (jobs, logins etc.) documented.
Just wanted to know what if we can have different solution other than fresh instance configuration.
BIG no-no. You should NEED to have backups (and don't let the SAN admins tell you that a LUN snapshot is sufficient, you NEED to be doing SQL Server backups...period).
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
June 16, 2011 at 7:50 am
Sqlchicken (6/16/2011)
viduvs (6/16/2011)
Thanks for your reply.I was having the same solution to install a new fresh instance and then configure the things.
Though we don't have a backup (even external backups), we have the things (jobs, logins etc.) documented.
Just wanted to know what if we can have different solution other than fresh instance configuration.
BIG no-no. You
shouldNEED to have backups (and don't let the SAN admins tell you that a LUN snapshot is sufficient, you NEED to be doing SQL Server backups...period).
Adding to what SQLChicken says, your situation is exactly why his statement is true.
June 16, 2011 at 8:12 am
One more thing.
How do I retore the databases in order to recover the data?
As we have lost LUN, we don't have database running up to take tail log backup.
We just have 1 full database backup and 3 transaction log backups taken after that. So how should i restore the database?
June 16, 2011 at 8:14 am
viduvs (6/16/2011)
One more thing.How do I retore the databases in order to recover the data?
As we have lost LUN, we don't have database running up to take tail log backup.
We just have 1 full database backup and 3 transaction log backups taken after that. So how should i restore the database?
The only thing you can do is restore the last full and restore the transaction log backups you have (in order). How old are those backups?
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
June 16, 2011 at 8:18 am
If you have issues in the restore because of the lack of tail logs, let us know and we'll try and figure it out from there. But go ahead and try to restore as many transaction logs as you can and see what happens before you borrow trouble.
FYI: Read the first three paragraphs here: http://msdn.microsoft.com/en-us/library/ms179314.aspx
June 16, 2011 at 10:58 am
I am going to use SQL Server management Studio to restore the database for point in time recovery.
So should i first restore the full database backup and then transaction log backups one by one?
June 16, 2011 at 11:20 am
Yup. All but the last log backup need to be restored WITH NORECOVERY. The last WITH RECOVERY
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2011 at 11:27 am
viduvs (6/16/2011)
I am going to use SQL Server management Studio to restore the database for point in time recovery.So should i first restore the full database backup and then transaction log backups one by one?
Yes. If the transaction log backups are part of the same backup chain, when you choose to restore the database SSMS should list all of the necessary files for restore and restore them in order for you.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
June 17, 2011 at 4:00 am
Thanks all for your help.
We could restore the instance without much data loss and now application is up and working fine.
June 17, 2011 at 4:43 am
Glad we were able to help.
June 17, 2011 at 2:48 pm
viduvs (6/16/2011)
SQL service for that instance is not starting up giving following error:"The SQL Server service terminated with service-specific error 17058 (0x42A2)"
Application logs show up an error that it could not open the sql server error log file.
Looks like you already went through a reinstall, but if the SQL Server service is throwing errors, then it exists, and wasn't lost with the dropped LUN.
Was this merely a case of the disk holding the error log being full? That will throw the same error.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply