SQl 2014 instance is DOWN all USER databases will not restore and SQLAGENT will not start. Urgent help required..
-
VMware ESXI 6.5
Windows 2012 R2 Data Center Cluster 2 Nodes
SQL 2014 EnterpriseI have iSCSI drives used for failover all works well.
Last night I got an alert saying my "E" drive was filing up.I opened SSMS and tried to shrink the databases. they all failed to shrink
I noticed a log file got very large the drive was 80 GB in size and the log file was 80 GBSo I decided to add another drive and move the databases over
In doing so the "E" dive disappeared from the server and I lost all databasesI then added a new "E" drive
ran a setup command setup /q /action=rebuilddatabase /instancename=mssqlserver /sqlsysaadminaccounts=sa /sapwd=xxxxxxxxx
Then stopped the sql service ran net start mssqlserver /m this started sql in single user mode
then used sqlcmd to restore the master database
1>restore database master from disk = 'f:\sqlbackup\master.ba withreplace
>GO
the database was successfully restored
Started the sqlserver service and was able to see my sql thru SSMS
No databases all in recovering pending
That's because I have no mdf or ldf file for the databasesI then restored the MSDB database which was suppose to get my sql jobs back
After restoring the MSDB
I tried to start the SQLAGENT and it failed
It shows (Agent XP's disabled.So I tried running this query
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Agent XPs',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override
go
It still failsIn the windows event log I see this error
Log Name: Application
Source: SQLSERVERAGENT
Date: 2/27/2019 9:36:21 PM
Event ID: 324
Task Category: Alert Engine
Level: Error
Keywords: Classic
User: N/A
Computer: TGCS014-N1.our.network.tgcsnet.com
Description:
Failed to initialize SQL Agent log (reason: Access is denied).
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="SQLSERVERAGENT" />
<EventID Qualifiers="49152">324</EventID>
<Level>2</Level>
<Task>4</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2019-02-28T02:36:21.000000000Z" />
<EventRecordID>108546</EventRecordID>
<Channel>Application</Channel>
<Computer>TGCS014-N1.our.network.tgcsnet.com</Computer>
<Security />
</System>
<EventData>
<Data>Failed to initialize SQL Agent log</Data>
<Data>Access is denied</Data>
</EventData>
</Event>
I went to the SQL Server Configuration Manager
Uppdated the password on the service account which is a domain account.Still get the same error
So I have two issues.
1. Can not start the sql agent
2. I can not restore any user database.Any help on either problem will be greatly appreciated
Thank you
Tom
-
Update Guys I finally got SQL Agent to Start it was a permissions issue on the drive to the path to the log folder
Now my only issue is restore the user database
When I try to restore the user database
Any ideas?
Thanks
-
If you lost the e: drive, adding it back doesn't necessarily mean the same e: drive mounts correctly. You need to find the mdf/ldf files first, or restore from backup.
You should not rebuild master to get this working. You want to fix the storage issue first, before you mess with SQL Server.
-
Steve,
The storage was destroyed I do not know how or why the drive filled up and caused some sort of corruption
I have only backup files and I am unable to restore them
I took the database offline
Ran this
restore database siteusers from disk = 'F:\SQL-BackupData\TGCS014-N1-SQLC\SiteUsers\FULL\TGCS014-N1-SQLC_SiteUsers_FULL_20190224_020054.bak' with norecovery,
move 'SiteUsers' to
'F:\SQL-Data\SiteUsers\SiteUsers.mdf'
errors
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "F:\SQL-Data\SiteUsers\SiteUsers.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'SiteUsers' cannot be restored to 'F:\SQL-Data\SiteUsers\SiteUsers.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'E:\SQL-Data\SiteUsers\SiteUsers_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 3156, Level 16, State 3, Line 1
File 'SiteUsers_log' cannot be restored to 'E:\SQL-Data\SiteUsers\SiteUsers_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I even tried on another sql server that was sql 2012 this is on 2014
How to I restore this is a mess been down for days now.
is there away using sqlcmd? maybe an interface issue?
Please help
-
you are obviously out of your league. I do advise you to ask for help from another DBA - or at least ask on the forums here before attempting to do something like this again.
your server is a cluster - as such only drives that are part of the cluster resources can be used to restore any database.
you said you added a new drive - so now next step is to add that drive as a cluster resource. Once that is done you will be able to use that drive to restore your db's.
you will also need to ensure that the SQL Server instance user has required permissions to the folder you are restoring to.
-
Why is it that no one knows how to restore the databases
Why do we back up the database if we can not restore them
All the disks are part of the cluster now even the new one.
I have the master and msdb restored from the backup drive.
Why cant I restore a user database.
Please help
- trgrassijr - Thursday, February 28, 2019 6:11 PM
You have to fix the server this all sits on before you can get anywhere. It's an issue with the cluster and the drives.
The error message you keep getting on the attempted restores is because SQL Server has a dependency on the added drive(s) so it needs to be added to the SQL Server resources. You really need to check the cluster itself and make sure that's configured correctly.Sue
-
it sounds like you did a lot of unnecessary work.
you lost the E drive, and should have looped in the SAN Admins to get the drive up. then you would not have needed to restore anything. they could have extended the E: drive and added some more space.
you shouldn't have restored master, should have worked to recover drives instead..I think you've added an E Drive, but you did not add it as a clustered resource.
you can only restore databases on drives that are part of the clusters storage, which is why you are getting that error.
after you get your clustered storage back in place, you should be able to restore again.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I do have the drive added to the cluster it is assigned to the Role
The disk is not a dependency it is assigned so I need to figure out how to make it a dependency
If any one knows let me know
Thanks
-
Guys
I figured it out
I am now able to restore user databases.
We can mark this as resolved.
Thanks for all the help
-
Glad it's working. I assume you added the drive as a dependency and found the folders?
-
yes I added it as a dependency
Was able to restore the databases from my .bak files.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply