I am totally clueless as to the workings of SQL. In spite that I have been using a program the "uses" SQL 2008 - I don't really understand how it works.
To wit: I recently found I was backing up ALL WRONG.
In 10/18/2018 I did a substantial update (merge/purge) of my DB and found - too late - that I had done it wrong and discovered I was not able to restore to the previous date.
I've been able (after much research and struggle) didn't to find the 10/17/2018 version of my data and I'm hoping I can' "restore" or otherwise get my data back to that version.
BELOW are the Directories of my DATA folder for 11/7/2018 and 10/17/2018 respectively.
It APPEARS only data in file GOLDMINE.mdf and file modellog.mdf have been altered (judging by size)
Can I simply copy the older GOLDMINE.mdf and modellog.mdf to resore my database to the earlier position?
I suspect it can't POSSIBLY be that easy.
TIA for helping my to screw this up anymore than I already have. Jim Gunther
I would suggest "restore" onto a new DB name and verify the backup first, make sure that it is what you want. Since you only have the mdf & ldf, you need to use "attach" DB method. Refer to Attach a Database
Thanks so much - I will ask a related question (for clarification) in a couple hours.
My impression is this is relatively simple to do but, I'd be surprised if such was really the case. As mentioned before, I don 't want to do something that will make things worse.
"See" you later (and thanks again).
So, am I on the right track?
(BTW: I won't be able to make any of these changes before the weekend).
I'll do a backup of everything that resides on my C drive to a removable HDD (I'm guessing Windows settings won't be reliable but, hoping all my SQL stuff will be saved<?>).
Then detach (and I have to do a bit more research on that process ):
a.) just Goldmine.mdf and modellog.ldf (the only files that APPEAR to have changes since the 10/17/18 version)?
b.) or should I detatch ALL the mdf and ldf files?
then attach the files (a or b) back the DATA folder?
presumably the attach proceedure is similar to Copy/Paste or MUCH more complicated?
Thanks again - Jim
You cannot back up those files because they are actually opened in SQL Server. If you somehow managed to copy/backup the files they would probably be useless because they would be missing data.
The appropriate way to backup a database is to implement a database backup process in SQL Server. If you are not performing daily full backups - and transaction log backups for full/bulk recovery models - then recovering to a prior point in time will not be possible.
A database backup is a command issued in SQL Server that creates a separate file that can then be used to restore from that file.
These tasks should be automated - a simplified approach is to use the maintenance plan wizard to generate the jobs. A more robust approach is to use scripts/procedures and agent jobs to specifically control how you want the maintenance to be performed.
You need to determine if you have database backups implemented - and if so, find those files. If this is a VM server - you can follow up with the server team to see if they are running VM backups - which may have been configured to include SQL Server database backups.
Jeff wrote >>>>You cannot back up those files because they are actually opened in SQL Server. If you somehow managed to copy/backup the files they would probably be useless because they would be missing da<<<<
Given that I have an Image of my entire Drive C (as of 10/17/18) - How 'bout I restore that entire image to a brand new SSD - then - replace the current C: Drive with the new one. Research indicates that all settings are - typically - captured on an image backup.....Presumably, all my SQL stuff would be good (and usable) as of 10/17/18.
See any pitfalls?
Thanks again for all the suggestions. Jim Gunther
Seems excessive. Either the backup will contain usable files or it won't - some backup software knows how to deal with SQL and will effectively manage the process well enough that the files contain data and others straight up won't. But if you have the files you can simply attach them with a new name, check to see what data is in there and whether it contains what you need.
If it looks ok, then you can detach both sets of files, replace the ones you don't want with the ones from the backup and re-attach them with the original name and all is good. Alternatively, you may be able to use the attached copy database to restore whichever bits of data you want back without losing other changes you have made in the mean time.
Either way, when you have it back how you want it you definitely want to spend some time setting up both data and log backups properly so that you can do point-in-time restores to whenever you might need (and spend a bit of time practicing the idea on a scratch database).
How was that image created? If the process to create the image of the drive does not account for SQL Server - then the mdf/ldf files could be empty or they could be corrupted or could be missing data.
Before you do anything - make sure you have a good backup of the current system and have that backup file saved off of the system. You want to insure you can recover the databases (all of them) if the image restore breaks SQL Server and prevents it from even starting up.
Once you have good backups - then you want to stop SQL Server services and copy all mdf/ldf files off the system so you have those available if needed.
Then - you can restore the image on C and see if SQL Server starts up and the databases are available. If that works and you have a working system - then immediately backup all databases. After that - you can restore the version that was purged with a different name - this will allow you to look at the differences between the tables and incorporate any data in the purged database (if needed).
SELECT S.[database_name], S.backup_start_date, S.backup_finish_date, M.physical_device_name
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
WHERE S.[type] = 'D' -- Full Backup (I = Diff, L = Log)
ORDER BY S.[database_name], S.backup_finish_date DESC;
Thanks for all the advice. Going to back up (Windows image) EVERYTHING, then, do the SQL back up (when I figure HOW to do it properly), then will install my NEW SSD as G drive and restore the 10/17/18 image to the new G Drive.
Then, I'll remove current C drive and replace it with the NEW SSD and boot up -- I'll check/confirm that everything is now on the new SSD and run my Goldmine program. Hopefully, it will have reverted to the 10/17/18 point in time.
If everything looks OK (fingers crossed), I'll do a "regular" backup - using the SQL routine.
AS a practical matter, I can probably "update" my 10/17/18 database manually. I've been pretty careful about adding a lot of new info or attachments since 10/17.
I'll keep y'all posted.
Well, I (think) I restored the 10/17/18 image to my brand new 500 Gig SSD.
All the files seem to be there but - when I put the new SSD in the C: drive ( I actually removed the current 250 gig SSD) it won't boot.
I'm researching the issue (apparently, pretty common when restoring images) and certainly appreciate any suggestions. It may be another 20 hours before I can get to it but, wanrted to leave an update. Thanks again Jim
There is a big difference between a copy of all the files on a disc and a genuinely bootable image of the drive. Again, it's not at all necessary to do that to validate the state of the SQL files in your image. Just attach them as a database (with a different name) and you can see what is currently stored in them.
Or get someone who knows what they are doing to evaluate your situation and recover what ever can be done.
Going down a rabbit hole IMHO. Sledge hammer to drive a needle. all you need is either the backups and/or mdf/ldf files. Restore backups into a sample database or use mdf/ldf on another server and try things out
I'm probably frustrating you guys as much as myself.
I've detached the current database and attached the Oct 17 version but it keeps reverting to the current version.
When I detached the current version I refreshed and verified that the current one wasn't there.
BTW: I've even changed the name of the database I'm trying to revert to "HarOct17" but it keeps coming back to "Harvard" the current one.
BTW: I'm pretty certain an "expert" will be able to fix it in the blink of an eye. A referral to an "expert" (for a small job would be appreciated.
I can't seem to post the Screen shots (Fairly long Word document). I would be in your debt if someone might review these off list.
My e-mail is JsqlG at RiskManagementSearch dot Com
a camera? or print screen?