Hi everyone
We've had a critical O/S issue on a server that runs SQL2012 Std Ed. Data is 100% intact, but server failed to boot after an update & restart. We couldn't get it back (long story but working theory is that EFI partition got corrupted when PSU failed while we were trying to fix the original issue) and had to eventually reinstall O/S.
I've now reinstalled SQL 2012 as well and want to start attaching all the DB's (I have the latest MDF and LDF files and everything appears to be intact - last shut down was controlled as this was originally for planned maintenance). My background is more dev than DBA, so a little out of my depth here.
Question I have is what is best practice here and what is contained in all the standard MS SQL databases (master, model, etc).
For instance, I don't know what jobs were running on the server - is this info in the master DB? If so, is it safe to replace the new master.mdf that the install created with my old one? Any specific procedure for this or can I simply shut down SQL, replace the file, start it up again? Is there a procedure like this to quickly restore everything (from a SQL point of view) to what it was before?
------------------------------------------
Anton
www.sqlsqllittlestar.com
Hi antonstar,
msdb holds Agent jobs. master holds logons, linked servers and system settings (and master database keys used to encrypt certificates), and model is the template database used when any new databases are created on the instance. I highly recommend restoring all of these databases.
https://www.sqlskills.com/blogs/jonathan/downgrading-sql-server-editions/ could help - this is a guide for reinstalling SQL Server to a lower SKU, which I used a couple of weeks back - it's similar to your situation in that you're restoring/recovering a SQL instance over a fresh install.
In it, Jonathan instructs to bring the fresh SQL Server version up to the exact same patch version as it was previously (you can get this from the first row of the ERRORLOG file in the \Log folder of the SQL Server installation if you still have access to the folder from before the issue), then stop the SQL Server service, and replace the data and log files as you've described.
As in the comments on that post, you'd want to make sure the SQL Service is running under the same user account as previously, and your reinstall is to the same directories. You want to restore all of the .MDF/.NDF and .LDF files to the same location they were previously, not just the master.mdf.
Best practice... err.. best practice might be to log a $500 support request with Microsoft.
Best of luck and seasons greetings,
Andrew
December 26, 2019 at 9:58 pm
Thanks, that's very useful.
Patch levels: That's not trivial this being a 2012 version and the media no longer being available for download, so I frankly just got fed up trying and went ahead and did it anyway and it actually worked beautifully, except for Reporting Services (issue with config and keys which seems to be stored in the Program files for reporting services). It looks like I've gotten around this by copying the old Program Files folder over Reporting Services, though I wouldn't recommend this, but this situation was just desperate enough to give it a go anyway.
We're going to move the DBs to a new server running 2017 in a few weeks, so as an interim I think we're fine.
------------------------------------------
Anton
www.sqlsqllittlestar.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply